Multiple left joins with count

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

SQL Server 2005 Forums

Transact-SQL (2005)

Multiple left joins with count

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-07-10 : 07:24:43

Hi Guru's,

Is it possible in SQL2005 to do multiple left joins with a count?

My attempt fails:

SELECT id, name, COUNT(tableB.id) + COUNT(tableC.id) AS recs
FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
LEFT JOIN tableC ON tableA.id = tableC.id
GROUP BY id, name

Basically, I am trying to get the SUM of the COUNT(tableB.id) + COUNT(tableC.id) AS recs for each row where the id in tableA may not appear in tableB or tableC or one or the other!

Thanks for your thoughts!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-10 : 07:32:21

[code]SELECT id, name,
SUM(CASE WHEN tableB.id IS NULL THEN 1 ELSE 0 END) +
SUM(CASE WHEN tableC.id IS NULL THEN 1 ELSE 0 END) AS recs
FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
LEFT JOIN tableC ON tableA.id = tableC.id
GROUP BY id, name[/code]

KH
[spoiler]Time is always against us[/spoiler]

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-07-10 : 07:33:43

Basically, this qry get compiles in qry analizer. Have u tried to execute this qry?

If possible, can u post some sample data?

Mahesh

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-07-10 : 08:35:59

Hi Mahesh and Khtan,

The query does work as does Khtan's version above however it duplicates counts with the same id. Take the following table structure:

TableA:

id
1
2
3
4

TableB

id
3
4
4
4
4
TableC

id
1
2
2
4
4

The result of the following query:

SELECT tableA.id,
SUM(CASE WHEN tableB.id IS NULL THEN 0 ELSE 1 END) +
SUM(CASE WHEN tableC.id IS NULL THEN 0 ELSE 1 END) AS recs
FROM tableA
LEFT JOIN tableB ON tableA.id = tableB.id
LEFT JOIN tableC ON tableA.id = tableC.id
GROUP BY tableA.id

Gives me a count of 16 'recs' for the id 4.

Am I missing something stupid
?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-10 : 09:26:14

quote:
Gives me a count of 16 'recs' for the id 4.

What is the expected count ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-10 : 09:28:42

quote:

Originally posted by John Sourcer


SELECT 	tableA.id, 
	SUM(CASE WHEN tableB.id IS NULL THEN 0 ELSE 1 END) +
	SUM(CASE WHEN tableC.id IS NULL THEN 0 ELSE 1 END)	AS recs
FROM	tableA
	LEFT JOIN tableB ON tableA.id = tableB.id
	LEFT JOIN tableC ON tableA.id = tableC.id
GROUP BY tableA.id

Why the change ?

KH
[spoiler]Time is always against us[/spoiler]

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-07-10 : 09:40:31

The expected count would be 6 surely? 4 in tableB and 2 in tableC!

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2007-07-10 : 09:42:23

That query returns 1 for each record where the id doesn't exist. I am trying to get the total of occurences of each id in both tableB and tableC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-10 : 09:45:17

quote:
Originally posted by John Sourcer

That query returns 1 for each record where the id doesn't exist. I am trying to get the total of occurences of each id in both tableB and tableC

Then the requirement is different from your initial post. The query will be different also.

Give it a try using RIGHT JOIN

KH
[spoiler]Time is always against us[/spoiler]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-10 : 09:45:24

You cannot join two transactional tables like that all in one SELECT. You must summarize them first in derived tables and then join it all together at the end:


SELECT 
  id, name, ISNULL(b.recs,0) + ISNULL(c.recs,0) as recs
FROM 
  tableA
LEFT JOIN 
  (select id, count(*) as recs from tableB group by id) b ON tableA.id = b.id
LEFT JOIN 
  (select id, count(*) as recs from tableC group by id) c ON tableA.id = c.id

This also makes your SQL more logical and there is no longer a need to group your primary (outer) SELECT.

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-10 : 09:46:00