[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SQL Select Where not exists help

Posted on 2007-07-29
Medium Priority
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

ID: 19589764
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

ID: 19589766
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19590165
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')
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 50

Expert Comment

ID: 19590498
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

ID: 19593035
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19593455
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 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 19596089
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 70

Expert Comment

by:Scott Pletcher
ID: 19596102
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

873 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