Post reply

not exist statement help!!

  • December 20, 2011 at 1:41 pm

    #247963

    Hi,

    I have 2 tables report (contains Raw data) and exceptions (filter for the Raw data). I'm trying to take "report" table and use the NOT EXISTS statement to filter with "exceptions" table but I'm having a problem.

    Select * from Report

    where NOT EXISTS ( select * from exceptions)

    No results are returned.

    do i have the right theory? please help.

  • December 20, 2011 at 1:49 pm

    #1423915

    bighow2008 (12/20/2011)

    Hi,

    I have 2 tables report (contains Raw data) and exceptions (filter for the Raw data). I'm trying to take "report" table and use the NOT EXISTS statement to filter with "exceptions" table but I'm having a problem.

    Select * from Report

    where NOT EXISTS ( select * from exceptions)

    No results are returned.

    do i have the right theory? please help.

    You are missing the join condition - how do you know which reports have exceptions? The exceptions table must have a column that identifies which report it belongs to, let's call it "report_id":

    select *

    from report r

    where not exists (select 1 from exceptions e where e.report_id = r.report_id)

  • December 20, 2011 at 1:49 pm

    #1423916

    usually, you use exists to detect a reference/relationship/join between the two:

    this is more like what i'd expect to see:

    SELECT

    *

    FROM Report

    WHERE NOT EXISTS (SELECT

    *

    FROM exceptions

    WHERE exceptions.SomeColumn = Report.Somecolumn)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 20, 2011 at 1:55 pm

    #1423919

    THANK!! WORKED PERFECT!!

    have a great holiday

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

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