Re: Re: Select with join query question

2003-07-28 Thread vze2spjf 
[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]

RE: Re: Select with join query question

2003-07-28 Thread Richard Bolen 
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]