Post reply

Not in search in multiple tables?

  • July 27, 2005 at 4:13 am

    #102664

    Hi there,

    I'm doing the following search in a table

    SELECT Municipality_No, Id_No

    FROM TableA

    WHERE Municipality_No NOT IN

       (SELECT Municipality_No

       FROM TableX)

    and everything works, but is it possible to make the same search in multiple tables and still know what table there was "not in" municipality numbers?

    Regrads

    Joejoe

  • July 27, 2005 at 4:24 am

    #577483

    I would change to use NOT EXISTS (less expensive).

    To know which table this was not found in 1 way is to:

    Have a bit column per all of the misc tables created in "TableA" with a DEFAULT of 0.  THEN update based on IF EXISTS to be 1.  PLEASE NOTE:  ALL values will need to be reset prior to running this (Assumption BATCH mode)

    Then you will be able to perform a SELECT FROM WHERE ALL BITs are 0..  You will also be able to determine where they are missing....

    If these need to exists across the board you may want to research Foreign Key constraints and this will help prevent data mismatches in the future



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 27, 2005 at 5:56 am

    #577509

    Thanks AJ,

    I will try it out

  • July 27, 2005 at 7:25 am

    #577522

    Just following this one up.  So this query:

    SELECT a.Municipality_No, a.Id_No

    FROM TableA a

    WHERE not exists (select x.Municipality_No from TableX x join TableA a on x.Municipality_No = a.Municipality_No)

    Is faster than this one

    SELECT Municipality_No, Id_No

    FROM TableA

    WHERE Municipality_No NOT IN

       (SELECT Municipality_No

       FROM TableX)

    ?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply