Combining queries with multiple joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Combining queries with multiple joins

Author

Topic

dthomas
Starting Member

2 Posts

Posted - 2012-01-17 : 16:25:12

Hi,

I am extracting data from our SQL Server through the R statistical package. I have a couple of queries with multiple joins which I need to combine into one dataset for analysis. The SQL for the separate queries are:

Query 1: CVD diagnosis

SELECT a.new_enc as ID, a.EVENDATE, a.Gender, a.ETHNICG1, a.diag01, a.diag02, a.diag03, a.diag04, a.diag05, a.diag06,
a.diag07, a.diag08, a.diag09, a.diag10, a.diag11, a.diag12, a.diag13, a.diag14, a.diag15
from LoadPUS a join CVD_ICD10$ b
on a.diag01 = b.[ICD-10 Codes]
OR a.diag02 = b.[ICD-10 Codes]
OR a.diag03 = b.[ICD-10 Codes]
OR a.diag04 = b.[ICD-10 Codes]
OR a.diag05 = b.[ICD-10 Codes]
OR a.diag06 = b.[ICD-10 Codes]
OR a.diag07 = b.[ICD-10 Codes]
OR a.diag08 = b.[ICD-10 Codes]
OR a.diag09 = b.[ICD-10 Codes]
OR a.diag10 = b.[ICD-10 Codes]
OR a.diag11 = b.[ICD-10 Codes]
OR a.diag12 = b.[ICD-10 Codes]
OR a.diag13 = b.[ICD-10 Codes]
OR a.diag14 = b.[ICD-10 Codes]
OR a.diag15 = b.[ICD-10 Codes]
Where (b.[ICD-10 Codes] Like 'E%' OR b.[ICD-10 Codes] Like 'G%'
OR b.[ICD-10 Codes] Like 'I%' OR b.[ICD-10 Codes] Like 'Z%')
AND CONVERT(SmallDateTime,a.EVENDate,103)>='2001-10-01' AND CONVERT(SmallDateTime,a.EVENDate,103)<='2011-09-30'
GROUP BY a.new_enc , a.EVENDATE, a.Gender, a.ETHNICG1,a.diag01, a.diag02, a.diag03, a.diag04, a.diag05, a.diag06, a.diag07, a.diag08, a.diag09, a.diag10, a.diag11, a.diag12, a.diag13, a.diag14, a.diag15

Query 2: CVD procedures

SELECT a.new_enc as ID, a.EVENDATE, a.Gender, a.ETHNICG1, a.diag01, a.op01, a.op02, a.op03, a.op04,
a.op05,a.op06, a.op07, a.op08, a.op09, a.op10,a.op11,a.op12,a.op13,a.op14,a.op15
from LoadPUS a join CVD_Procedures$ b
on a.op01 = b.[Trimmed Op Codes]
OR a.op02 = b.[Trimmed Op Codes]
OR a.op03 = b.[Trimmed Op Codes]
OR a.op04 = b.[Trimmed Op Codes]
OR a.op05 = b.[Trimmed Op Codes]
OR a.op06 = b.[Trimmed Op Codes]
OR a.op07 = b.[Trimmed Op Codes]
OR a.op08 = b.[Trimmed Op Codes]
OR a.op09 = b.[Trimmed Op Codes]
OR a.op10 = b.[Trimmed Op Codes]
OR a.op11 = b.[Trimmed Op Codes]
OR a.op12 = b.[Trimmed Op Codes]
OR a.op13 = b.[Trimmed Op Codes]
OR a.op14 = b.[Trimmed Op Codes]
OR a.op15 = b.[Trimmed Op Codes]
WHERE (CONVERT(SmallDateTime,a.EVENDate,103)>='2001-10-01' AND CONVERT(SmallDateTime,a.EVENDate,103)<='2011-09-30')
AND (a.op01 <> '')
GROUP BY a.new_enc , a.EVENDATE, a.Gender, a.ETHNICG1, a.op01, a.op02, a.op03, a.op04, a.op05,a.op06, a.op07, a.op08, a.op09, a.op10,a.op11,a.op12,a.op13,a.op14,a.op15

I need to combine all of the records from query 1 and all of the records in query 2 which are not in query 1 based on new_enc, EVENDATE, Gender, ETHNICG1. I'm relatively new to creating complex SQL queries and I'm not sure of the correct SQL for this. You help would be appreciated.
D

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-17 : 16:27:58

Hello dthomas,

Would The UNION operator with some filtering help you in this case?

dthomas
Starting Member

2 Posts

Posted - 2012-01-17 : 19:57:42

Hi ehorn,

Thanks for you reply. UNION works if i were to omit all the diag and op codes from the output. However i wish to include all 15 diag and all 15 op codes in the final output which makes the query tricky to construct which i haven't been able to do so far.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources