SELECT *,SUM(s.status) AS ActiveJob FROM Jobs AS j LEFT JOIN Submissions AS
s ON j.job_id=s.job_id
WHERE ActiveJob<1 OR ActiveJob IS NULL
GROUP BY j.job_id
I'm not sure what type of data is in your status field, so I'm not sure if SUM is the right thing you are looking. The above query should give you all jobs without any Sumission records and those that have matching submission records but whose status ends up to be zero. You may not need to check for NULL values, I forget if MySQL considers NULL less than 1 or anything else. I don't think it does.
On Monday, July 28, 2003, at 04:12 PM, Richard Bolen wrote:
I think this gets me all the Jobs that have no submissions but I'm
really looking for any job that doesn't have a submission with a status
of 1. That means I need Jobs that don't have submissions plus jobs with
submissions with exclusively non-1 statuses.
The problem is when a job has more than one submission associated with it (and at least one submission has a non-1 status).
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
