retrieve NOT related data many to many relation
I have 3 tables.
one of the tables is a linked table, so it contains the primary key from table 1 and primary key from table 2 as foreign keys. together they make up the primary key for the linked table.
Now i need to find out which items are not in the linked table.
example : table A contains camping facilities, table B contains availability periods, table C is the cominbination of the chosen camping facility with a specific period. Now i need to know which facilities and periods are still available (all combination not in table C)
Software/Hardware used:
Software/Hardware used:
ASKED:
January 21, 2008 11:01 AM
UPDATED:
January 24, 2008 5:18 PM
Answer Wiki:
One way to find all the rows in table a that do not have a match in table c is to use a NOT IN subquery.
<pre>
SELECT ...
FROM a
WHERE facility_id NOT IN
( -- Begin sub-query of all facility_ids in table c
SELECT facility_id
FROM c
WHERE facility_id IS NOT NULL
) -- End sub-query of all facility_ids in table c
;
</pre>
Do another, similar query for table b.
NOT IN is a short-cut for doing not-equals operations: "facility_id NOT IN (1, 2)" is equivalent to "facility_id <> 1 AND facility_id <> 2", and "facility_id NOT IN (subquery)" is equivalent to asserting that facility_id is not equal to any row returned by the sub-query. Note that if any row returned by the sub-query is NULL, then SQL can't make that assertion, so remember check for NULLs in the sub-query.
To see all answers submitted to the Answer Wiki: View Answer History.
a simple outer join will show you the unmatched rows as they will have nulls in the “common” columns