Return 0 or # rows between 2 tables

Last Post 19 Sep 2007 05:16 AM by SQLUSA. 12 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
kj2w2ndAccount
New Member
New Member

--
18 Sep 2007 07:01 AM
Here is my select statement:

SELECT
v.COL1,
count(*) as 'COUNT'
FROM
table1 v
INNER JOIN table2 a ON v.COL1 = a.COL1
GROUP BY v.COL1

here are my results:

Col1 COUNT
----------------
a 1
c 4
d 5
g 1
... ...
z 3

how can i get my results to look like this:

Col1 COUNT
----------------
a 1
b 0
c 4
d 5
e 0
f 0
g 1
... ...
z 3

?
kj2w2ndAccount
New Member
New Member

--
18 Sep 2007 07:05 AM
Is there a better answer than this:

SELECT
v.COL1,
(SELECT count(*) FROM TABLE2 a WHERE v.COL1 = a.COL1 )
as [COUNT]
FROM
TABLE1 v
GROUP BY v.COL1

???
nosepicker
New Member
New Member

--
18 Sep 2007 07:46 AM
Try this method:

SELECT
v.COL1,
SUM(CASE WHEN a.COL1 IS NOT NULL THEN 1 ELSE 0 END)
FROM
table1 v
LEFT JOIN table2 a ON v.COL1 = a.COL1
GROUP BY v.COL1
kj2w2ndAccount
New Member
New Member

--
18 Sep 2007 08:44 AM
nosepicker, thanks for the reply but your suggested method actually returns the same thing that I had earlier:

Col1 COUNT
----------------
a 1
c 4
d 5
g 1
... ...
z 3

and not

Col1 COUNT
----------------
a 1
b 0
c 4
d 5
e 0
f 0
g 1
... ...
z 3

Any idea?
nosepicker
New Member
New Member

--
18 Sep 2007 10:56 AM
I guess I made the wrong assumptions about your data. Can you provide some sample data from both tables?
SQLUSA
New Member
New Member

--
18 Sep 2007 02:28 PM
You need to create and populate a #temptable where you have all the values with 0 count.

You then run an update using the query above for non-zero counts.

Alternate is UNION .

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com/order2005grandslam

SQLUSA
New Member
New Member

--
18 Sep 2007 08:28 PM
A third option: RIGHT JOIN

Here is an example: http://www.sqlusa.com/bestpractices...countzero/


SwePeso
New Member
New Member

--
19 Sep 2007 04:49 AM
OMG! Temp tables and RIGHT JOIN?

DECLARE @Table1 TABLE (Col1 INT)

INSERT @Table1
SELECT 1 UNION ALL
SELECT 2

DECLARE @Table2 TABLE (Col1 INT)

INSERT @Table2
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 3

SELECT t1.Col1,
COUNT(t2.Col1) AS [COUNT]
FROM @Table1 AS t1
LEFT JOIN @Table2 AS t2 ON t2.Col1 = t1.Col1
GROUP BY t1.Col1
SwePeso
New Member
New Member

--
19 Sep 2007 04:55 AM
quote:

Originally posted by: kj2w2ndAccount
nosepicker, thanks for the reply but your suggested method actually returns the same thing that I had earlier
Nosepicker's solution works. Try it with the test data I posted earlier.

I don't think you actually tried it.
Nosepickers solution returns same resultset as mine and your's with subquery.



SQLUSA
New Member
New Member

--
19 Sep 2007 05:16 AM
Very impressive Peter! You are a true sql genius!

LEFT JOIN instead of RIGHT JOIN!

@tablevariable instead of #temptable

UNION ALL instead of UNION!

Next time I will make sure I include all possibilities for the sake of forum members still in kindergarten!

SQLUSA
New Member
New Member

--
22 Sep 2007 08:41 AM
Thanks Peter K.

That was really interesting.

DBCC DROPCLEANBUFFERS

exec V1 1000000

-- took 5 sec (table variables )

DBCC DROPCLEANBUFFERS

exec T1 1000000 (temp tables)

-- took 4 sec

Generally acceptable wisdom, @tablevariable is faster due to no logging like #temptable.

I always wondered though...SQL Server has to have a buffer limit on table variables and will start writing to disk when over limit?


Kalman Toth, MCDBA, MCITP ( administrator, developer, bi developer)
SQL Server 2005 Training: http://www.sqlusa.com/order2005grandslam
SwePeso
New Member
New Member

--
23 Sep 2007 11:26 PM
Yes, that limit is about 2 pages.
When more, our table variables flushes to tempdb and kills performance.
SwePeso
New Member
New Member

--
23 Sep 2007 11:30 PM
Right JOIN will work too, since there are only two tables in this case.

SELECT t1.Col1,
COUNT(t2.Col1) AS [COUNT]
FROM @Table2 AS t2
RIGHT JOIN @Table1 AS t1 ON t1.Col1 = t2.Col1
GROUP BY t1.Col1
You are not authorized to post a reply.

Acceptable Use Policy