---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...
--
--1234677 'bob' 'smith' '10:00:00' '11:00:00'
--1234
--677
--1235677 'mike' 'williams' '10:00:00' '11:00:00'
--1236
--5554447
--12365554447 'debra' 'stone' '10:30:00' '11:30:00'
-- 1238
--5585888
--1237446 'ken' 'marwood' '11:00:00' '12:00:00'
--12385585888 'bill' 'shireton' '11:15:00' '11:15:00'
--12395585888 'laura' 'acree' '11:15:00' '12:15:00'
--12405585888 '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 677 'bob' 'smith'
--1236 5554447 'debra' 'stone'
--1237 446 '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]
