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). Something like this should work. You want to do a left join on Jobs so you don't filter out those without submission matches. The resulting left join will have a value of NULL for any fields joined from Submissions that don't have a match in Jobs. Just include at least on field from Submissions and test for null on that field. SELECT *,s.status AS ActiveJob FROM Jobs AS j LEFT JOIN Submissions AS s ON j.job_id=s.job_id WHERE s.status IS NULL On Monday, July 28, 2003, at 02:37 PM, Richard Bolen wrote: > I'm trying to write a select query that involves 2 tables. One table > (Submissions) has a one to many relationship with the other table > (Jobs). I'm trying to find all the records in Jobs that do NOT have a > corresponding record in Submissions with a status of 1. > > The problem I'm having is that when there is more than one record in > Submissions that match a record in Jobs and one Submissions record has > a status of 1 and one record doesn't, my query matches the one with > status > != 1 and returns the record for Jobs (even though it has a record in > Submissions with a status of 1 also). > > I've tried a variety of queries including left outer joins and more > simple join relationships. I'm using MySQL 3.23.47 on Windows. > > Here's an example query: > > select j.job_id from jobs j left outer join submissions s on (j.job_id > = > s.job_id) where s.status_id != 1 group by j.job_id > > Any help is greatly appreciated. > > Rich > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- 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]
