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/mysql?unsub=1
>
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
