SQL rewrite UNION to LEFT JOIN

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

Database Design and Application Architecture

SQL rewrite UNION to LEFT JOIN

Author

Topic

RaoulDuke
Starting Member

1 Post

Posted - 2012-04-12 : 10:09:39

Hi, I am trying to figure out how to rewrite code using LEFT JOIN instead of UNION (see the example). The problem is that I have never encountered this before and my brain and google aint giving me much to work with.

I have tried but the results are to awkward to post here since it was a couple of years ago i worked with SQL. Would really appreciate your help!

SELECT a_patientkey, COUNT(b_drugkey) AS antal 
FROM tabela, tabelb 
WHERE b_patientkey = a_patientkey 
GROUP BY a_patientkey 
UNION 
SELECT a_patientkey, 0 AS antal 
FROM tabela
WHERE a_patientkey NOT IN 
(SELECT b_patientkey 
FROM tabelb)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-12 : 10:18:49

do you mean this?

SELECT
	a.a_patientkey,
	COUNT(b.b_drugkey) AS antal
FROM
	tablea a
	LEFT JOIN tabelb b ON a.a_patientkey = b.b_patientkey
GROUP BY
	a.a_patientkey

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-04-12 : 10:25:31

I think you have gotten a little confused. Is this what you are looking for?



	
DECLARE @patient TABLE (
	[PatientKey] CHAR(1)
	, [PatientName] VARCHAR(50)
	)

DECLARE @Prescription TABLE (
	[PatientKey] CHAR(1)
	, [DrugKey] CHAR(1)
	, [DrugName] VARCHAR(50)
	)
	
INSERT @patient ([PatientKey], [PatientName])
VALUES ('A', 'Andrew'), ('B', 'Boris')

INSERT @Prescription ([PatientKey], [DrugKey], [DrugName])
VALUES ('A', 'M', 'Morphine'), ('A', 'P', 'Paracetamol')

SELECT * FROM @patient
SELECT * FROM @Prescription

SELECT
	pat.[PatientKey] AS [PatientKey]
	, COUNT(pre.[DrugKey]) AS [DrugKey]
FROM
	@patient As pat
	LEFT OUTER JOIN @Prescription as Pre ON Pre.[PatientKey] = pat.[PatientKey]
GROUP BY
	pat.[PatientKey]

Some things to note:

1) I use JOIN's (ansi syntax)
2) I use aliases for the tables (pat and pre)
3) because there is no match on patient to prescription for Boris -- you get back a null. A Count on NULLS gives you a 0

so the result are:


PatientKey DrugKey
---------- -----------
A          2
B          0

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources