Problem showing all records

Question

Problem showing all records

intralucent

Valued Member

Points: 56
More actions
June 13, 2003 at 8:54 am

#80635

Hi...I know this is really dumb, but I have a query that should (I think) be pulling all 60 records into a dataset I've built in Visual Basic. There are three child tables. Code is below. Why do I only get about 12 records to show. What would prohibit all 60 records from showing. Also in the three child tables, some of them do not have associated records with the parent table (because the use didn't enter anything - is not required to). Any help or light on the situation would be appreciated.
SELECT Precertification.idsPrecertification AS Expr1, Precertification.dtmCertDate AS Expr2, Precertification.idsEmployee AS Expr3,
Precertification.idsDependent AS Expr4, Precertification.idsProvider AS Expr5, Precertification.idsProviderLocAddress AS Expr6,
Precertification.idsProviderBillAddress AS Expr7, Precertification.PracticeName AS Expr8, Precertification.DoctorName AS Expr9,
Precertification.PracticeAddress1 AS Expr10, Precertification.PracticeAddress2 AS Expr11, Precertification.PracticeCity AS Expr12,
Precertification.PracticeState AS Expr13, Precertification.PracticeZip AS Expr14, Precertification.txtPhysicianPhone1 AS Expr15,
Precertification.txtPhysicianPhone2 AS Expr16, Precertification.txtPhysicianContactName AS Expr17, Precertification.txtDaysApproved AS Expr18,
Precertification.memNotes AS Expr19, Precertification.intLKPResolved AS Expr20, Precertification.idsHospital AS Expr21,
Precertification.idsHospitalLocAddress AS Expr22, Precertification.txtHospital AS Expr23, Precertification.txtHospitalAddress1 AS Expr24,
Precertification.txtHospitalAddress2 AS Expr25, Precertification.txtHospitalCity AS Expr26, Precertification.txtHospitalState AS Expr27,
Precertification.txtHospitalZip AS Expr28, Precertification.txtHospitalPhone1 AS Expr29, Precertification.txtHospitalPhone2 AS Expr30,
Precertification.txtHospitalPhone3 AS Expr31, Precertification.txtHospitalContact AS Expr32, Precertification.blnIP AS Expr33,
Precertification.bln23hr AS Expr34, Precertification.blnOP AS Expr35, Precertification.blnER AS Expr36, Precertification.blnApproved AS Expr37,
Precertification.blnDenied AS Expr38, Precertification.txtDeniedCode AS Expr39, Precertification.dtmAdmitDate AS Expr40,
Precertification.dtmDCDate AS Expr41, Precertification.txtPOTReceived AS Expr42, Precertification.blnUsed AS Expr43,
Precertification.txtUserCreate AS Expr44, Precertification.dtmCreate AS Expr45, Precertification.txtUserEdit AS Expr46,
Precertification.dtmEdit AS Expr47, Precertification.txtPatientIsInsured AS Expr48, Precertification.txtPatientName AS Expr49,
Precertification.txtEmployeeName AS Expr50, Precertification.txtEmployeeSSN AS Expr51, Precertification.txtEmployeeStatus AS Expr52,
Precertification.txtGroupNumber AS Expr53, PrecertificationDiagnosis.*, PrecertificationService.*, CommentsPrecertification.*
FROM Precertification INNER JOIN
PrecertificationDiagnosis ON Precertification.idsPrecertification = PrecertificationDiagnosis.idsPrecertification INNER JOIN
PrecertificationService ON Precertification.idsPrecertification = PrecertificationService.idsPrecertification INNER JOIN
CommentsPrecertification ON Precertification.idsPrecertification = CommentsPrecertification.PrecertificationIdx
ORDER BY Precertification.txtEmployeeName
Thanks, Mike

Thanks, Mike

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi intralucent,

without digging deeper. You said there are 60 records to be shown, but not for all parent values are there entries in the child tables. And you're using INNER JOIN. This will only work where parent and child values exactly match, eg. your 12 records. Try some LEFT or RIGHT JOIN (not sure, I always mix these up)

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

intralucent Valued Member Points: 56 More actions · Answer 2

Hi Frank,

There are 60 records in the parent table, 21 in one child, 27 in another child and 40 in the third child. Does that help?

Thanks for your reply and that was quick !!!!

Thanks, Mike

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hi Mike,

quote:
There are 60 records in the parent table, 21 in one child, 27 in another child and 40 in the third child. Does that help?

just doing time waiting for the weekend

Maybe the easiest way is to create a view within EM. You can add the tables in question, use drag and drop on the fields that are to be joined, right-click on the join line and check 'All rows from <your_parenttable>. Next choose the fields you want to be displayed. SQL Server will create the correct syntax, open the view by clicking on the red exclamation mark and you should see your records. Maybe you want to save the view for future use?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

RonKyle SSC-Dedicated Points: 31574 More actions · Answer 4

quote:
FROM Precertification INNER JOIN
PrecertificationDiagnosis ON Precertification.idsPrecertification = PrecertificationDiagnosis.idsPrecertification INNER JOIN
PrecertificationService ON Precertification.idsPrecertification = PrecertificationService.idsPrecertification INNER JOIN
CommentsPrecertification ON Precertification.idsPrecertification = CommentsPrecertification.PrecertificationIdx

Change to:

FROM Precertification p

LEFT JOIN PrecertificationDiagnosis pd

ON p.idsPrecertification = pd.idsPrecertification

LEFT JOIN PrecertificationService ps

ON p.idsPrecertification = ps.idsPrecertification

LEFT JOIN CommentsPrecertification cp

ON p.idsPrecertification = cp.PrecertificationIdx

I assume that the 60 records are in the Precertification table. If so this will return all 60 records, with Nulls in the fields where there is not match. I have used alias for clarity, but you will not be able to use these unless you use them throughout the SQL statement.

Edited by - RonKyle on 06/16/2003 08:43:29 AM