query question...

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.

thanks!



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: query question...

It sounds to me like you want to join the two tables?
http://dev.mysql.com/doc/refman/5.1/en/join.html

On Mon, Jun 15, 2009 at 03:56, bruce wrote:
> hi.
>
> i've got a situation, where i'm trying to figure out how to select an ite=
m
> from tblA that may/maynot be in tblB.
>
> if the item is only in tblA, i can easilty get a list of the items
> =A0select * from tblA
>
> if the item is in tblA but not linked to tblB, i can get the items as wel=
l
> =A0select * 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 =3D tblB.aid.
>
> thoughts/pointers on this would be appreciated.
>
> thanks!
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmartijn@cryst=
al-labs.nl
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: query question...

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=gcdmg-mysql-2@m.gmane.org