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 开发者_如何学JAVAdo this?
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;
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]
;
精彩评论