Join for three tables in MSSQL

[poojavb]

Hello Friends,

I have three table
->PatientDetails
->ParentDetails
->InsuranceDetails

-Patient id is the common column in all the three tables...

While saving the data in table using VB.Net there is a column in PatientDetails i.e. Insured

If the patient is insured then only the values from text boxes and the patient id will be saved in InsuranceDetails Table else the patientid wont exist in that table

But I want to retrieve data from all the three tables even if the patient is not insured the blank columns shud be atleast shown to the user

for that I had written a query as below but it is not showing any values

please check my query and help me on this...I just used a join for patientid

select 
    ipd.InPatientID,
    ipd.Fname+' ' +ipd.LName as 'Patient Name' ,
    ipd.Gender,
    ipd.BirthDate,
    ipd.AccType as 'Account Type',
    ipd.Minor, 
    ipg.GFName+' ' +ipg.GLName as 'Guardian name',
    ipd.Insured,
    ipi.Insurance1,
    ipi.Policy1,
    ipi.GroupNo1, 
    ipi.Guarantor,
    ipi.Employer 
from 
    HMS.dbo.PatientDetails ipd, 
    HMS.dbo.ParentDetails ipg, 
    HMS.dbo.InsuranceDetails ipi 
where 
    ipd.InPatientID=ipg.InPatientID or 
    (ipd.InPatientID=ipi.InPatientID and 
    ipi.InPatientID=ipg.InPatientID)

Please provide some help on this....

Thanks in advance

[seslie]

Hi poojavb,

I can't see a 'join' clause anywhere in your query. You can use this syntax

FROM table1 join table2
ON table1.primarykey = table2.foreignkey join table3
ON table2.primarykey = table3.foreignkey

(or try changing your where clause to see if it works..worked for my sample table, although i didnt use the same columns

where ipd.InPatientID=ipg.InPatientID and     
      (ipd.InPatientID=ipi.InPatientID or ipi.InPatientID=ipg.InPatientID)

)

[darkagn]

If InsuranceDetails can be null, you need to use a LEFT JOIN to join your tables together.

SELECT 
  ipd.InPatientID,
  ipd.Fname+' ' +ipd.LName as 'Patient Name' , -- this line is dangerous if name(s) can be null
  ipd.Gender,
  ipd.BirthDate,
  ipd.AccType as 'Account Type',
  ipd.Minor,
  ipg.GFName+' ' +ipg.GLName as 'Guardian name', -- same here
  ipd.Insured,
  ipi.Insurance1,
  ipi.Policy1,
  ipi.GroupNo1,
  ipi.Guarantor,
  ipi.Employer
from
  HMS.dbo.PatientDetails ipd
inner join -- inner join means all conditions in "on" must be true
  HMS.dbo.ParentDetails ipg
on
  ipd.InPatientID = ipg.InPatientID
left join -- left join means the "on" can be true or null for ipi (right of the join)
  HMS.dbo.InsuranceDetails ipi
on
  ipd.InPatientID = ipi.InPatientID

[poojavb]

seslie - I tried ur query but it returned the rows only if patientid is present in Insurance details table

darkagn - Your query worked absolutely fine....

Thank you both for ur help