List:General Discussion« Previous MessageNext Message »
From:Richard Bolen Date:July 28 2003 8:12pm
Subject:RE: Select with join query question
View as plain text  
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

Thread
Select with join query questionRichard Bolen28 Jul
  • Re: Select with join query questionBrent Baisley28 Jul
RE: Select with join query questionRichard Bolen28 Jul
  • Re: Select with join query questionBrent Baisley28 Jul
Re: Select with join query questionvze2spjf28 Jul
  • Re: Select with join query questionBruce Feist28 Jul