SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    identifying records not in a table

    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.

    Code MySQL:
    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.

    Code Oracle8:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •