Unanswered: mysql column represent a dynamic query?
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
Human Resources (8)
Information Technology (0)
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 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.