Re: sql getting orphan records
- From: "Paul Lautman" <paul.lautman@xxxxxxxxxxxxxx>
- Date: Fri, 30 Jun 2006 14:00:42 +0100
julian_m wrote:
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
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!
.
- References:
- sql getting orphan records
- From: julian_m
- sql getting orphan records
- Prev by Date: Re: PHP inserting extra data at start of response body
- Next by Date: Re: Open-save .php file error instead of execute?
- Previous by thread: sql getting orphan records
- Next by thread: treemenu on quickform
- Index(es):
Relevant Pages
|
