Hi Bruce,
bruce wrote:
hi.
i've got a situation, where i'm trying to figure out how to select an item
from tblA that may/maynot be in tblB.
if the item is only in tblA, i can easilty get a list of the items
select * from tblA
if the item is in tblA but not linked to tblB, i can get the items as well
select * from tblA where id not in (select id from tblB);
but i have no idea how to combine the two selects..
i need to combine them, as the app can create tblA for a given item, and
then later on create the data in tblB, with thblA.id = tblB.aid.
thoughts/pointers on this would be appreciated.
Just as the other reply said, you need a JOIN. Specifically you need one
of the OUTER JOIN such as LEFT JOIN or RIGHT JOIN. Here's the pattern
for "exists in A and is/is not in B" (using the fields you specified in
your sample)
SELECT ...
FROM a
LEFT JOIN b
ON a.id = b.aid
Any row where b.id is null would indicate a mismatch (the row exists in
A but not in B). So you can test for things like
"all rows in A that have no match in B"
SELECT ...
FROM a
LEFT JOIN b
ON a.id = b.aid
WHERE b.id IS NULL
"all rows in B that have no match in A"
SELECT ...
FROM b
LEFT JOIN a
ON a.id = b.aid
WHERE a.id IS NULL
- or -
SELECT ...
FROM a
RIGHT JOIN b
ON a.id = b.aid
WHERE a.id is null
"only rows from A or B that have a matching row in in the other table"
SELECT ...
FROM a
INNER JOIN b
ON a.id = b.id
"All Rows from A and only those matching rows from B where b.datecol >
'2009-06-13' "
SELECT ...
FROM a
LEFT JOIN b
ON a.id = b.id
AND b.datecol > '2009-06-13'
- this will NOT work -
SELECT ...
FROM a
LEFT JOIN b
ON a.id = b.id
WHERE b.datecol > '2009-06-13'
It will not work because the WHERE clause will filter from the results
any rows from A where b.datecol is not > '2009-06-13' which would
include those rows from A that had no matching row from B (effectively
turning the LEFT join into an INNER join). Try it both ways and see.
Yours,
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
