identifying records not in a table

[IBazz]

Hi,

I have just realised I am missing a foreign key constraint in one of my tables.

so now I need to find what records of the menus (child), table, are not in the menus_sequencer (parent), table.

this query gives me back nearly 400 reaulsts, which is just about all of them and surely, that is not the correct result.

SELECT m.business_id
, m.content_category
, m.menu_name
 FROM menus as m
left outer 
join menus_sequencer as ms
on ms.business_id = m.business_id
WHERE 
ms.content_category != m.content_category
and ms.menu_name != m.menu_name

This next query gives me over 5000 results when there are only 68 and 84 records respectively, in each table.

SELECT m.business_id
, m.content_category
, m.menu_name
 FROM menus AS m
left outer 
join menus_sequencer AS ms
ON ms.content_category = m.content_category
WHERE 
ms.menu_name != m.menu_name

what way should I be writing this query?

bazz

[r937]

what you forgot to mention is the foreign key, tsk tsk

however, having seen a few of your previous threads, i'm gonna guess that it's a composite key, and from the first query you posted, i'm gonna guess that it's a 3-column composite key

SELECT m.business_id
     , m.content_category
     , m.menu_name
  FROM menus as m
LEFT OUTER 
  JOIN menus_sequencer as ms
    ON ms.business_id      = m.business_id
   AND ms.content_category = m.content_category
   AND ms.menu_name        = m.menu_name
 WHERE ms.business_id IS NULL

the part in red is where the query attempts to make the correct join

the part in blue is where you detect that the join didn't find a matching row