469,625 Members | 1,089 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

mysql getting orphan records


I'm working with mysql without referential itegrity.
Let me make some small example:

tableA
+-----------+---------------+
| id_1 | data_1 |
+-----------+---------------+
| 1 | bla1 |
| 2 | bla2 |
| 3 | bla3 |
+-----------+---------------+

tableB

+-----------+------------+
| id_2 | fk_1 |
+-----------+------------+
| 2_1 | 1 |
| 2_2 | 3 |
| 2_3 | 6 |
+-----------+------------+

Every record on tableA, should have at least 1 record on tableB, linked
trhough fk_1, but as times goes

on, and due users doing wrong things, there could be problems.

For instance, in the above example, there is one record in tableA
(id_1=2) that has no related record on

tableB , and there is one record in tableB (id_2=2_3) whose fk_1 value
doesn't exists on tableA

Now the question:
1) How could I select all records on tableA which has no related
records on tableB?
2) How could I select all records on tableB which has no related
records on tableA?

I've been tring with left and right joins, but I can't figure out how
to do in order to get just "orphan"

records instead full sets

regards - julian

Jun 30 '06 #1
2 3812
julian_m wrote:
Now the question:
1) How could I select all records on tableA which has no related
records on tableB?
SELECT tableA.id_1
FROM tableA LEFT OUTER JOIN tableB ON tableA.id_1 = tableB.fk_1
WHERE tableB.fk_1 IS NULL;
2) How could I select all records on tableB which has no related
records on tableA?


SELECT tableB.id_2
FROM tableA RIGHT OUTER JOIN tableB ON tableA.id_1 = tableB.fk_1
WHERE tableA.id_1 IS NULL;

Regards,
Bill K.
Jun 30 '06 #2
Many thanks Bill.

regards- julian

Jun 30 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by jackiu | last post: by
reply views Thread by John Thorne | last post: by
reply views Thread by Mike Chirico | last post: by
3 posts views Thread by auron | last post: by
5 posts views Thread by RioRanchoMan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.