RE: Select with join query question
This gives the count per job which is always 1. I'm using the 'having' clause which requires the 'status' field in the select list. This makes it difficult to get a total. I'll play with the 'where' clause example to see if that works. Thanks again! -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 7:42 PM To: Richard Bolen Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Select with join query question Richard Bolen wrote: This works! I was then wondering how to get the total number of all jobs that this condition is true for? Just include count(distinct j.jobid) in the SELECT list. Bruce 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
Richard Bolen wrote: This gives the count per job which is always 1. Oops! Quite right. I don't see a way to get the total off-hand. Bruce -Original Message- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Monday, July 28, 2003 7:42 PM To: Richard Bolen Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Select with join query question Richard Bolen wrote: This works! I was then wondering how to get the total number of all jobs that this condition is true for? Just include count(distinct j.jobid) in the SELECT list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
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]
RE: Select with join query question
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/[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]
Re: Select with join query question
From: Richard Bolen [EMAIL PROTECTED] Date: 2003/07/28 Mon PM 01:37:27 CDT To: [EMAIL PROTECTED] Subject: Select with join query question 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 haven't been able to think of a clever way to do it using only JOINs. Moreover, there are probably clever/elegant solutions using subselects (that is, nested queries), but your version of MySQL doesn't have those. Here's how I would do it. (Sorry if my notation deviates from yours a little): CREATE TEMPORARY TABLE tmp SELECT jobs.id AS jobs_id FROM jobs LEFT JOIN sub ON jobId=jobs.id WHERE status=1; SELECT jobs.id FROM jobs LEFT JOIN tmp ON jobs.id=jobs_id WHERE jobs_id IS NULL; 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select with join query question
[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]
Re: Select with join query question
I was wondering if you were going to come back with that. Your going to need to using grouping then. Something like this should do it: SELECT *,SUM(s.status) AS ActiveJob FROM Jobs AS j LEFT JOIN Submissions AS s ON j.job_id=s.job_id WHERE ActiveJob1 OR ActiveJob IS NULL GROUP BY j.job_id I'm not sure what type of data is in your status field, so I'm not sure if SUM is the right thing you are looking. The above query should give you all jobs without any Sumission records and those that have matching submission records but whose status ends up to be zero. You may not need to check for NULL values, I forget if MySQL considers NULL less than 1 or anything else. I don't think it does. On Monday, July 28, 2003, at 04:12 PM, Richard Bolen wrote: 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). -- 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]
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]
RE: Re: Select with join query question
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]
Re: Select with join query question
Richard Bolen wrote: This works! I was then wondering how to get the total number of all jobs that this condition is true for? Just include count(distinct j.jobid) in the SELECT list. Bruce 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
