I've 3 tables. One table has all of the people, [Pat], each having a unique [PatId]. The 2nd table has all of the insurance provider information, [Ins], each having a unique [InsId]. The 3rd table has got the patient insurance info, [PatIns]. Within the [PatIns] table, some patients (also [PatId]) have secondary or 3rd insurance also it denoted in [InsType] as 1, 2, or 3. I want a SQL query that won't only join the three tables, but additionally return the information whenever a patient has secondary or third insurance. To date I've:


FROM [XEREX_TEST].[dbo].[xrxPat], 



[XEREX_TEST].[dbo].[xrxPatIns] AS INS2,

[XEREX_TEST].[dbo].[xrxPatIns] AS INS3 

WHERE [xrxPat].[PatId]=[xrxPatIns].[PatId] 

AND [xrxPatIns].[PatId] = INS2.[PatId] 

AND [xrxPatIns].[PatId] = INS3.[PatId]

AND [xrxIns].[RecNo]=[xrxPatIns].[InsId] 

AND [xrxPatIns].[InsType]=1

AND INS2.[InsType]=2 

AND INS3.[InsType]=3;   

Issue is this only returns patients with 3 insurance policies. I must return all of the patients and null values for tables INS2 and/or INS3 when the patient has only 1 insurance. Any idea how to get this done?

While this may be completed in the where clause, you are better off altering to presenting explicit joins, as which makes the code simpler to see too.

FROM [XEREX_TEST].[dbo].[xrxPat]
INNER JOIN [XEREX_TEST].[dbo].[xrxIns]
    ON [xrxPat].[PatId] = [xrxIns].[PatId]
INNER JOIN [XEREX_TEST].[dbo].[xrxPatIns]
    ON [xrxIns].[RecNo] = [xrxPatIns].[InsId]
    AND [xrxPatIns].[InsType] = 1
LEFT JOIN [XEREX_TEST].[dbo].[xrxPatIns] AS INS2
    ON [xrxIns].[RecNo] = INS2.[PatId]
    AND INS2.[InsType] = 2
LEFT JOIN [XEREX_TEST].[dbo].[xrxPatIns] AS INS3
    ON [xrxIns].[RecNo] = INS3.[PatId]
    AND INS3.[InsType] = 3;

Make use of the JOIN notation rather than commas. Make them LEFT JOINs.

(SELECT * FROM [XEREX_TEST].[dbo].[xrxPatIns] WHERE [InsType]=1) AS INS1
(SELECT * FROM [XEREX_TEST].[dbo].[xrxPatIns] WHERE [InsType]=2) AS INS2
ON INS1.[PatId] = INS2.[PatId]
(SELECT * FROM [XEREX_TEST].[dbo].[xrxPatIns] WHERE [InsType]=3) AS INS3
ON INS1.[PatId] = INS3.[PatId]
ON [xrxPat].[PatId]=INS1.[PatId] 
ON [xrxIns].[RecNo]=INS1.[InsId]