SQL counting

[transparent]

I have a user table as follows called tblUsers:
-----------------------------------
| USER_ID | LOCATION | JOBTYPE |
-----------------------------------
| 1 | north | mechanic |
-----------------------------------
| 2 | west | sales |
-----------------------------------
| 1 | north | mechanic |
-----------------------------------

and a location table called tblJobs


| JOBTYPE |
------------
| sales |
------------
| mechanic |
------------
|management|

I want to produce a record set that contains the number of occurrances of the jobs defined in table tblJobs within table tblUsers for a specific location (ie north). I wrote the following sql:


select tblJobs.jobtype, count(tblUsers.jobtype) as count from tbljobs, tblusers where tblUsers.jobtype=tbljobs.jobtype and location="north" group by tbljobs.jobtype

This works, but returns the results:

|JOBTYPE | COUNT |
-----------------
|mechanic| 2 |
-----------------

however I require

|JOBTYPE | COUNT |
-----------------
|mechanic| 2 |
-----------------
|sales | 0 |
-----------------

How do I do this?

Cheers for any help!

 

[carp]

I think an outer join will do the job for you. The exact syntax for this will depend on the RDBMS you are using.

 

[crufty]

Remove '... and location = "north" ...', it's filtering out all locations != "north"

 

[transparent]

it should be tblUsers.location - I need it to sort via location...

With regards to outer joins - this won't work with a mysql db!

cheers anyway.