sql getting orphan records

[julian_m]

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

[Paul Lautman]

Re: sql getting orphan records

julian_m wrote:[color=blue]
> I'm working with mysql without referential itegrity.
> Let me make some small example:
>
> tableA
> +-----------+---------------+[color=green]
>> id_1 | data_1 |[/color]
> +-----------+---------------+[color=green]
>> 1 | bla1 |
>> 2 | bla2 |
>> 3 | bla3 |[/color]
> +-----------+---------------+
>
> tableB
>
> +-----------+------------+[color=green]
>> id_2 | fk_1 |[/color]
> +-----------+------------+[color=green]
>> 2_1 | 1 |
>> 2_2 | 3 |
>> 2_3 | 6 |[/color]
> +-----------+------------+
>
> 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[/color]

If you've got at least MySQL 4.1 then

SELECT * FROM tableA as a where NOT EXIST SELECT * FROM tableB as b WHERE
a.id_1 = b.fk_1

and vice versa for the other orphans

Alternatively for older versions of MySQL

SELECT * FROM tableA as a LEFT JOIN tableB as b ON a.id_1 = b.fk_1 WHERE
..bfk_1 IS NULL

and vice versa for the other orphans

For future reference, the comp.databases. mysql group is a better bet for
questions on MySQL. The
comp.lang.php group is best for questions on PHP!