Re: Select with join query question

[Brent Baisley]

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]