Here's an example:

we have two tables, one is called job_categories which has two columns:
job_category_id (integer, primary key, auto increment)
job_category_title (title of the job category)

second table is titled jobs, which is a list of jobs. One of the columns is job_category_id to correspond to the category it belongs in. Say we want to get a query that will list each job category title individually, and then how many jobs exist in the jobs table with that category_id. i.e

Architecture (3)
Human Resources (8)
Information Technology (0)
Miscellaneous (19)

Is there any way to do that query without having to do a count query individually on each row returned in some sort of a loop? I don't have any of my SQL books here (I'm at home with the family for Christmas, brought the powerbook with me).

Thanks that worked great. Can you possibly gimme a brief explanation of how that join is working, or have a good reference site handy?

Actually, I notice even if 0 records exist for a category that query still returns 1 for each category. Any suggestions?

ooops, my bad

change count(*) to count(jobs.job_category_id)

Thanks, man. Is there a way to limit it to only return locations that have more than 0 jobs associated with that location id?

yes, change LEFT OUTER JOIN to INNER JOIN

Thanks again. One more revisit to this, say I have another table called Employers with an employer_active field (1 or 0) and I want to only have this query represent jobs who have an employer_id of an employer whose employer_active = 1

table employer has employer_id, employer_active, etc.
job has job_id, employer_id, etc.

oops I forgot to mention I want this to be represented in the location count as well, i.e

Say There are 10 jobs listed under maryland, but only 5 are active, then it would show

job_location_name = maryland
num_jobs = 5

job, categories, employers, locations ... i'm lost now -- what are you counting again?

OK I'll try to explain, thanks for your patience!

Table Jobs has (among others):
job_id, employer_id, job_active, job_location_id

table job_locations has (among others):
job_location_id, job_location_name

table employers has (among others):
employer_id, employer_active

I want to essentially get a list in the format:

Alabama (2)
Arkansas (3)
etc.,

the count should represent how many jobs have that location id, have job_active = 1, and employer_id of an employer who has employer_active = 1 . Does this make sense?