> > 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]
