Re: Select with join query question

[Bruce Feist]

[EMAIL PROTECTED] wrote:

From: "Richard Bolen" <[EMAIL PROTECTED]>
Date: 2003/07/28 Mon PM 01:37:27 CDT
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.
   

I haven't been able to think of a clever way to do it using only JOINs.

Rich's solution, which I edited out, was a good one.  But, if you really 
want to do it with a single JOIN, try this:

select j.*
FROM Jobs j LEFT JOIN Submissions s ON j.jobid = s.jobid
GROUP BY /* all selected columns */
HAVING min(abs(s.status - 1)) > 0
I leave it as an exercise to the reader to figure out why this works (if 
it does -- I haven't tested it!).  If status = 1 is the lowest possible 
value for status, you can simplify this a bit.

Bruce Feist



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]