SQL Count Help

seamus

Right, I know enough SQL to do some useful functions for myself, but when it gets a little advanced, I get lost.

I have two tables. One is called Handset, which has two field - ID (int, primary key) and Name (VARCHAR).
The second is called Subscriber. In the Subscriber table, there's a column called handset, which corresponds to a value in Handset.ID

So I'm trying to create a query that returns me three columns - Handset.ID, Handset.Name, and the third column, which is the number of occurrences of Handset.ID in Subscriber.Handset.

What I currently have is

SELECT Handset.id, Handset.name, count(Subscriber.handset) from Subscriber, Handset WHERE Handset.id = Subscriber.handset GROUP BY Handset.id

This returns something like what I need, except that it only returns values where count > 0. That is, if nobody is using a handset, it's not returned. I need a list of all handsets, and the number of users using them, zero or otherwise. I know it's the WHERE Handset.id = Subscriber.handset that's messing it up, but as I say, I don't know enough to know how to proceed.

Any ideas?
Thanks

tempest

SELECT Handset.id, Handset.name, count(Subscriber.handset) from Subscriber, Handset WHERE Handset.id = Subscriber.handset(+) GROUP BY Handset.id

if memory serves.

Eitherway google for Outer Join to get the details on the difference in the statements.

tempest

Actually it's a right outer join so the plus goes on the left..

SELECT Handset.id, Handset.name, count(Subscriber.handset) from Handset, Subscriber WHERE Handset.id(+) = Subscriber.handset GROUP BY Handset.id

also join syntax for this is:

SELECT Handset.id, Handset.name, count(Subscriber.handset) from Handset RIGHT OUTER JOIN Subscriber ON Handset.id = Subscriber.handset GROUP BY Handset.id 

seamus

tempest wrote:

....

Thanks for that tempest. Twould seem there's multiple ways of doing this

The following worked for me

SELECT Handset.id, Handset.name, count(Subscriber.handset) from Handset LEFT OUTER JOIN Subscriber ON Handset.id = Subscriber.handset GROUP BY Handset.id

Which is identical to your second example, just with LEFT/RIGHT and tables switched.

Thanks to PaschalNee too for pointing me in the right direction. That JOIN syntax is going to comin handy for other things.

bonkey

tempest wrote:

Actually it's a right outer join so the plus goes on the left

Heh.

The OUTER table of an OUTER JOIN is the one which will allow non-matching records to be included.

That would make it a LEFT OUTER JOIN if you do FROM Handset...as Seamus found out

As an aside, its worth noting that in one of the pre-processing steps, the MSSQL optimiser rewrites all RIGHT OUTER JOINS as LEFT OUTER JOINS. This isn't an optimisation per se, it just means that the actual optimiser has only one "direction" of outer join to worry about.

What this means is that - if you're like me - you may find it easier to never think of LEFT and RIGHT OUTER JOINS, but rather write everything using LEFT OUTER JOIN.

As a second aside, I dunno what DB you're using, but just for your own information, you should check if it supports FULL OUTER JOIN. You need it rarely, but when you do...it generally saves a bucket of performance and/or work.

jc

tempest

bonkey wrote:

Heh.

The OUTER table of an OUTER JOIN is the one which will allow non-matching records to be included.

That would make it a LEFT OUTER JOIN if you do FROM Handset...as Seamus found out

Aye, I still have trouble deciding which left foot to put the left shoe on, let alone joining in SQL.