-->-----Original Message----- -->From: Larry Brown [mailto:[EMAIL PROTECTED] -->Sent: Monday, October 27, 2003 11:32 AM -->To: MySQL List -->Subject: illusive query --> -->Earlier I was given help understanding the need for using a left join. -->This -->was a precursory query to arrive at my final solution which I had not -->touched on since I believed that by getting the join correct I could get -->the -->result. It seems to be evading me though. Still using the following -->example table.. --> -->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' --> 1238 -->5585888 -->1237 4455556 'ken' 'marwood' '11:00:00' '12:00:00' -->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 -->whose -->reference numbers do not match the reference numbers that are linked with -->the controlnum's from table two together with all of the records in table -->two. I can't follow that description and I wrote it! Maybe an -->example... -->This is the result I want... --> -->1234 6666677 'bob' 'smith' -->1236 5554447 'debra' 'stone' -->1237 4455556 'ken' 'marwood' -->1238 5585888 'bill' 'shireton' --> -->So the result set does not include a record such as 1235 because it's -->reference number matches a reference number from a record from the same -->table referenced by table two. It includes all other records.
Well the hint to let you know what to do is that the data is in the 2 tables AND you want all the NON matching ref numbers. >From this hint you need a LEFT OUTER JOIN or LEFT JOIN SELECT t2.* FROM t1 LEFT JOIN t2 ON t1.ref = t2.ref WHERE t2.ref is NULL; Give me everything from t2 where ALL the rows in t2 do not match the rows in t1. --> --> --> -->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]
