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]
