This works! I was then wondering how to get the total number of all jobs that this condition is true for?
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 5:26 PM To: Bruce Feist Cc: Richard Bolen; [EMAIL PROTECTED] Subject: Re: Re: Select with join query question [snip] > 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. A quick test seems to show it works. Though it doesn't pick up the case where status IS NULL, which occurs when there's a job but no matching submission. One disadvantage to your method: it requires computing a formula for each tuple, which slows things down (in principle; not sure it really matters in practice). > > Bruce Feist > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
