Identifying records not in a table

Databases
1

Hi,

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

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

2

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

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
    [COLOR="Red"]ON ms.business_id      = m.business_id
   AND ms.content_category = m.content_category
   AND ms.menu_name        = m.menu_name[/COLOR]
 WHERE [COLOR="Blue"]ms.business_id IS NULL[/COLOR]

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