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