SQL Query Source Code

Question

I have 3 tables. One table has all the people, [Pat], each with a unique [PatId]. The second table has all the insurance company information, [Ins], each with a unique [InsId]. The third table has the patient insurance info, [PatIns]. In the [PatIns] table, some patients (also [PatId]) have secondary or 3rd insurance and it denoted in [InsType] as 1, 2, or 3. I need a SQL query that will not only join the 3 tables, but also return the data when a patient has secondary or third insurance. So far I have:

SELECT * 

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

[XEREX_TEST].[dbo].[xrxIns],

[XEREX_TEST].[dbo].[xrxPatIns]

[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;

Problem is this only returns patients with 3 insurances. I would like to return all the patients and null values for tables INS2 and/or INS3 if the patient only has 1 insurance. Any idea how to do this?

lins314159 · Accepted Answer · 2010-01-26 02:02:54Z

2

While this could be done in the where clause, you're best off changing to using explicit joins, as that makes the code easier to read as well.

SELECT
  *
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;

edited Jan 26, 2010 at 2:02

answered Jan 26, 2010 at 0:38

lins314159

2,5201 gold badge16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nathan H Over a year ago

will this correctly handle all 3 cases: the customer has 1, 2, or 3 policies?

SDwebs Over a year ago

Running the query I get: 'The multi-part identifier "xrxPatIns.PatId" could not be bound.' Not that familiar with SQL, how I fix it?

JeffO Over a year ago

Line 5 should be: ON [xrxPat].[PatId] = [xrxIns].[PatId]

Rob Farley · Accepted Answer · 2010-01-26 00:44:04Z

0

Use the JOIN notation instead of commas. Then make them LEFT JOINs.

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

answered Jan 26, 2010 at 0:44

Rob Farley

15.9k5 gold badges46 silver badges61 bronze badges

Collectives™ on Stack Overflow

SQL Query Source Code

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related