SQL Select Where not exists help

Posted on 2007-07-29
Last Modified: 2008-01-09
Good Evening


I am in need of some help with a SQL query.  Here are the 2 I have that I need to put into one:
1)Select x,y from Table1 where y = '01'
2)Select x,y,z from Table2 where y = '01' and z = '099-1001'

I want to do something like this but I am not getting the correct results:
Select x,y from Table1 where y='01' and not exists(Select x,y,z from Table2 where y = '01' and z = '099-1001')

What do you think?

Question by:Jimbo99999
    LVL 15

    Expert Comment

    Could you clarify what exactly you want returned in the query? Do you want to return all items from #1 that aren't #2? If so, is there a foreign key that links the 2 tables (column x?)
    LVL 29

    Expert Comment

    not exists? Your intro suggests that you want to return them but your example would exclude?
    Is there any relationship between these 2 tables (ys?) or do you just want to return 2 different selects in a single resultset?

    select x,y,null
    from table1 A
    where A.y='01'
    union all
    select x,y,z
    from table2 B
    where B.y = '01' AND B.z = '099-1001'

    The first select has a third column set to NULL as union selects must have an equal number of columns returned
    where y
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I guess you are looking for this:
    Select t1.x, t1.y
    from Table1 t1
    where t1.y='01'
    and not exists(Select null from Table2 t2 where t2.y = t1.y and t2.x = t1.x and t2.z = '099-1001')
    LVL 50

    Expert Comment

    you need to correllate the subquery to the outer query...
    otherwise they are independant and the result for the subquery  is independant of the outer query  (ie does that condition exist anywhere within the "Whole Table")

    so angeliii's example is the one you probably want...


    Author Comment

    Good Morning


    I appreciate the responses from everyone.  However, I am getting no results but should be returning 1 record using Angellll's example.  What information should I provide to assist with the debugging?

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    the best would be the create table script and some insert statements that show the sample data that you have in your table, so we can test on our systems, and eventually see what is wrong.
    LVL 68

    Accepted Solution

    SELECT table1.x, table1.y
    FROM Table1
    LEFT OUTER JOIN Table2 ON table1.y = table2.y AND table2.z = '099-1001'
    WHERE table2.y IS NULL
    LVL 68

    Expert Comment

    Or, if you prefer the NOT EXISTS specifically:

    SELECT table1.x, table1.y
    FROM Table1
        SELECT 1
        FROM Table2
        WHERE table1.y = table2.y AND table2.z = '099-1001'
    --WHERE table1.y = '01'  --optional

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now