Re: unexpected results from query between tables
You need a LEFT JOIN select f.controlnum,f.referencenum,f.fname,f.lname from first f, left join second s on f.controlnum = s.controlnum where s.controlnum IS NULL and f.inputtime '07:00:00' Larry Brown wrote: ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unexpected results from query between tables
The simple JOIN (which is what you have requested with the A, B syntax) builds a conceptual table in which every row in A is paired with every row in B, then passes the result on to the WHERE filter. Of course, it doesn't actually do that, because it would take an enormous time, but it mimics that behaviour. Therefore, in the simple join there will be a massive number of rows, but none where the second is null. To get an entry where the second is null is the province of the LEFT JOIN, which forces an entry for every row in the first (left) table even if there is no entry in the second (right) table. You can then use the null-ness of the second table in the WHERE field. Try something on the lines of SELECT f.controlnum, f.referencenum, f.fname, f.lname FROM first f LEFT JOIN ON f.controlnum = s.controlnum WHERE s.controlnum IS NULL AND f.inputtime '07:00:00' |-+--- | | Larry Brown | | | [EMAIL PROTECTED]| | | tworks.com | | | | | | 27/10/2003 15:24| | | | |-+--- --| | | | To: MySQL List [EMAIL PROTECTED] | | cc: | | Subject: unexpected results from query between tables | --| I apparently am misunderstanding how the select works by referencing data in two different tables. I have used a similar statement to the one that follows with success, but there must be something different here that reveals a lack of fundamental understanding as to how it works. If someone could help, please check the following... I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234 677 'bob' 'smith' '10:00:00' '11:00:00'1234677 1235 677 'mike''williams' '10:00:00' '11:00:00'12365554447 1236 5554447 'debra' 'stone' '10:30:00' '11:30:00'1237446 1237 446 'ken' 'marwood' '11:00:00' '12:00:00'12385585888 1238 5585888 'bill''shireton' '11:15:00' '11:15:00' 1239 5585888 'laura' 'acree' '11:15:00' '12:15:00' 1240 5585888 'dora''lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' the results I get back are such as... 1234 677 'bob' 'smith' 1234 677 'bob' 'smith' 1235 677 'mike''williams' 1235 677 'mike''williams' 1235 677 'mike''williams' 1235 677 'mike''williams' 1236 5554447 'debra' 'stone' and so on... I apparently, ignorantly, thought I would get only those records to which the controlnum was not in both tables and which had an inputtime that is greater than 7 which would not filter any more out in this example. Also, this is for explination purposes. The actual tables are much larger, the only fields that actually exist on the first table to the actual table are control,ref,lname,fname and the second table has quite a few other fields that do not exist in the first table. I just simplified things to find out where my understanding fails for the logic behind the query. Thanks for any help. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --
Re: unexpected results from query between tables
You are using the wrong syntax, try SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; The query, you are using produces cross join while you need to implement left join for your problem. Enjoy Nitin - Original Message - From: Larry Brown [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Monday, October 27, 2003 8:54 PM Subject: unexpected results from query between tables I apparently am misunderstanding how the select works by referencing data in two different tables. I have used a similar statement to the one that follows with success, but there must be something different here that reveals a lack of fundamental understanding as to how it works. If someone could help, please check the following... I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234 677 'bob' 'smith' '10:00:00' '11:00:00' 1234 677 1235 677 'mike' 'williams' '10:00:00' '11:00:00' 1236 5554447 1236 5554447 'debra' 'stone' '10:30:00' '11:30:00' 1237 446 1237 446 'ken' 'marwood' '11:00:00' '12:00:00' 1238 5585888 1238 5585888 'bill' 'shireton' '11:15:00' '11:15:00' 1239 5585888 'laura' 'acree' '11:15:00' '12:15:00' 1240 5585888 'dora' 'lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' the results I get back are such as... 1234 677 'bob' 'smith' 1234 677 'bob' 'smith' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1235 677 'mike' 'williams' 1236 5554447 'debra' 'stone' and so on... I apparently, ignorantly, thought I would get only those records to which the controlnum was not in both tables and which had an inputtime that is greater than 7 which would not filter any more out in this example. Also, this is for explination purposes. The actual tables are much larger, the only fields that actually exist on the first table to the actual table are control,ref,lname,fname and the second table has quite a few other fields that do not exist in the first table. I just simplified things to find out where my understanding fails for the logic behind the query. Thanks for any help. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- 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: unexpected results from query between tables
Thank you all. The world makes sense again. :) Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 27, 2003 10:54 AM To: [EMAIL PROTECTED] Cc: MySQL List Subject: Re: unexpected results from query between tables The simple JOIN (which is what you have requested with the A, B syntax) builds a conceptual table in which every row in A is paired with every row in B, then passes the result on to the WHERE filter. Of course, it doesn't actually do that, because it would take an enormous time, but it mimics that behaviour. Therefore, in the simple join there will be a massive number of rows, but none where the second is null. To get an entry where the second is null is the province of the LEFT JOIN, which forces an entry for every row in the first (left) table even if there is no entry in the second (right) table. You can then use the null-ness of the second table in the WHERE field. Try something on the lines of SELECT f.controlnum, f.referencenum, f.fname, f.lname FROM first f LEFT JOIN ON f.controlnum = s.controlnum WHERE s.controlnum IS NULL AND f.inputtime '07:00:00' |-+--- | | Larry Brown | | | [EMAIL PROTECTED]| | | tworks.com | | | | | | 27/10/2003 15:24| | | | |-+--- --- ---| | | | To: MySQL List [EMAIL PROTECTED] | | cc: | | Subject: unexpected results from query between tables | --- ---| I apparently am misunderstanding how the select works by referencing data in two different tables. I have used a similar statement to the one that follows with success, but there must be something different here that reveals a lack of fundamental understanding as to how it works. If someone could help, please check the following... I have two tables. One table has entries controlnum,referencenum,fname,lname,inputtime,outputtime the second table has controlnum,referencenum. In table one referencenum can have and does have duplicates. The second table is populated with a subset of data from the first table but referencenum is unique. For instance... 1234 677 'bob' 'smith' '10:00:00' '11:00:00'1234677 1235 677 'mike''williams' '10:00:00' '11:00:00'12365554447 1236 5554447 'debra' 'stone' '10:30:00' '11:30:00'1237446 1237 446 'ken' 'marwood' '11:00:00' '12:00:00'12385585888 1238 5585888 'bill''shireton' '11:15:00' '11:15:00' 1239 5585888 'laura' 'acree' '11:15:00' '12:15:00' 1240 5585888 'dora''lindsey' '11:15:00' '12:15:00' ok, now I want to run a query that results in all of the controlnum's in table one that are not in table two. The query I ran was select f.controlnum,f.referencenum,f.fname,f.lname from first f,second s where f.controlnum != s.controlnum and f.inputtime '07:00:00' the results I get back are such as... 1234 677 'bob' 'smith' 1234 677 'bob' 'smith' 1235 677 'mike''williams' 1235 677 'mike''williams' 1235 677 'mike''williams' 1235 677 'mike''williams' 1236 5554447 'debra' 'stone' and so on... I apparently, ignorantly, thought I would get only those records to which the controlnum was not in both tables and which had an inputtime that is greater than 7 which would not filter any more out in this example. Also, this is for explination purposes. The actual tables are much larger, the only fields that actually exist on the first table to the actual table are control,ref,lname,fname and the second table has quite a few other fields that do not exist in the first table. I just simplified things to find out where my understanding fails for the logic behind the query. Thanks for any help. Larry S. Brown Dimension Networks, Inc. (727) 723-8388 -- 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
