Select Count when not exist

Question

Post reply

Select Count when not exist

MM_SQLQuestion

SSC Rookie

Points: 38
More actions
January 8, 2013 at 5:36 am

#287304

Experts
Table1 with fields: LinkField, NameField
Table2 with fields: LinkField, OtherField
I am trying to select the Table1.NameField and the count of records in table2 for a specific LinkField values
i.e.
Select Distinct(NameField), Count(*) as recordcount from
(
select NameField from Table1, Table2
where Table1.LinkField = Table2.LinkField
and Table2.OtherField = 'Somthing'
and Table2.LinkField IN (1,2,3,4)
) t
group by NameField
Now this will return 4 records if we got all the required values of the LinkField (1,2,3,4) but if one value is not missing it will only return 3 records. i.e. if there is not records for the LinkField = 4 in both table then it will not show anything for that...
What I am trying to do is for such case to show Zero as the record count....
Any advice please?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 1

Without DDL and sample data, this is just a guess

select Table1.NameField,count(Table2.LinkField) as recordcount

from Table1

left outer join Table2 on Table1.LinkField = Table2.LinkField

and Table2.OtherField = 'Somthing'

and Table2.LinkField IN (1,2,3,4)

group by Table1.NameField

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

davidandrews13 SSCertifiable Points: 6855 More actions · Answer 2

i think it would be like this.

select NameField,COUNT(table2.LinkField) FROM

Table1

LEFT JOIN Table2 ON Table1.LinkField = Table2.LinkField

and Table2.OtherField = 'Somthing'

and Table2.LinkField IN (1,2,3,4)

GROUP BY namefield

MM_SQLQuestion SSC Rookie Points: 38 More actions · Answer 3

This is returning all the records in Table1 not only the records of LinkField IN (1,2,3,4)

So I had to change it to

select NameField,COUNT(table2.LinkField) FROM

Table1

LEFT JOIN Table2 ON Table1.LinkField = Table2.LinkField

and Table2.OtherField = 'Somthing'

and Table2.LinkField IN (1,2,3,4)

where Table1.LinkField IN (1,2,3,4)

GROUP BY namefield

davidandrews13 SSCertifiable Points: 6855 More actions · Answer 4

dont you just hate it when someone else comes in with the answer before you? 😀

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

MM_SQLQuestion (1/8/2013)
This is returning all the records in Table1 not only the records of LinkField IN (1,2,3,4)
So I had to change it to
select NameField,COUNT(table2.LinkField) FROM
Table1
LEFT JOIN Table2 ON Table1.LinkField = Table2.LinkField
and Table2.OtherField = 'Somthing'
and Table2.LinkField IN (1,2,3,4)
where Table1.LinkField IN (1,2,3,4)
GROUP BY namefield

-- you don't need to double-up on the filter, it's done by the join:

select NameField, COUNT(table2.LinkField)

FROM Table1

LEFT JOIN Table2

ON Table2.LinkField = Table1.LinkField

and Table2.OtherField = 'Somthing'

--and Table2.LinkField IN (1,2,3,4)

where Table1.LinkField IN (1,2,3,4)

GROUP BY namefield

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden