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]
