Results 1 to 2 of 2
Apr 2, 2011, 07:32 #1
- Join Date
- Sep 2008
- 0 Post(s)
- 0 Thread(s)
identifying records not in a table
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.
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?
Apr 2, 2011, 09:01 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 3 Thread(s)
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 blue is where you detect that the join didn't find a matching row