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 6666677 'bob' 'smith' '10:00:00' '11:00:00' 1234 6666677 1235 6666677 'mike' 'williams' '10:00:00' '11:00:00' 1236 5554447 1236 5554447 'debra' 'stone' '10:30:00' '11:30:00' 1237 4455556 1237 4455556 '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 6666677 'bob' 'smith' 1234 6666677 'bob' 'smith' 1235 6666677 'mike' 'williams' 1235 6666677 'mike' 'williams' 1235 6666677 'mike' 'williams' 1235 6666677 '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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
