sql 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

.

Relevant Pages

  • Re: Cursors (again)
    ... What does it matter if TableB is temporary? ... or tableA. ... open cTableB ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL: Working with huge tables of chronological data
    ... TableB is ~30 millions row and contains (timestamp, ... from tableA TA1 left outer join tableB TB on (TA1.timestamp < ... that portion of the plan was executed. ...
    (comp.databases.oracle.server)
  • Re: Dazed and Confused
    ... related to the records in TableB, *and* how EACH record in TableB is related ... to the records in TableA. ... Purchase Order Details Table to a Purchase Order Table ... Junction table to the Purchase Requisition table. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: SQL: Working with huge tables of chronological data
    ... TableB is ~30 millions row and contains (timestamp, ... from tableA TA1 left outer join tableB TB on (TA1.timestamp < ... the predicate information for ID 9 come from? ...
    (comp.databases.oracle.server)
  • Re: Subselect Query Problem
    ... tableA LEFT JOIN tableB ... the result (of the inner join), it is logically re-introduced into the ... result, but since nothing match in tableB, whatever comes from tableB is ... > is Table B that has the first three fields as the primary key, ...
    (microsoft.public.access.queries)