January 22, 2016 at 3:24 pm
Hello,
I am trying to combine multiple sets of tables and then compare with not exists, this query doesn't return an error, but also doesn't return any results, which it should. What is wrong?
Thanks in advance..
select *
from
(select [dept],[name],[age]
from TableA
union
select [dept],[name],[age]
from TableB) as T1
where not exists
(select [dept],[name],[age]
from TableC
union
select [dept],[name],[age]
from TableD as T2
where T1.name=T2.name )
January 22, 2016 at 3:33 pm
it would help a lot of you posted some dummy data for your tables. (CREATE TABLE and INSERT scripts).
Also, do the UNIONS and the NOT EXISTS separately. Maybe cheat and create views for your chunks and then combine them later (and then drop the views).
January 22, 2016 at 3:46 pm
VegasL (1/22/2016)
Hello,I am trying to combine multiple sets of tables and then compare with not exists, this query doesn't return an error, but also doesn't return any results, which it should. What is wrong?
Thanks in advance..
select *
from
(select [dept],[name],[age]
from TableA
union
select [dept],[name],[age]
from TableB) as T1
where not exists
(select [dept],[name],[age]
from TableC
union
select [dept],[name],[age]
from TableD as T2
where T1.name=T2.name )
The problem is that you only use WHERE to restrict the rows in TableD to rows that match names in T1 (the union of TableA and TableB). TableC has no WHERE clause at all. So, as long as there are any rows at all in TableC, the NOT EXISTS subquery will return rows. If the subquery returns rows, NOT EXISTS is false, so no rows will be returned.
A quick example of how to fix that (along with an example of how to give us sample data in the future) is this:
CREATE TABLE #TableA (dept int, name char(10),age int);
CREATE TABLE #TableB (dept int, name char(10),age int);
CREATE TABLE #TableC (dept int, name char(10),age int);
CREATE TABLE #TableD (dept int, name char(10),age int);
INSERT INTO #TableA (dept,name,age) VALUES (1,'Jones',30);
INSERT INTO #TableB (dept,name,age) VALUES (2,'Smith',20);
INSERT INTO #TableC (dept,name,age) VALUES (1,'Jones',30);
INSERT INTO #TableD (dept,name,age) VALUES (3,'Quincy',40);
select *
from
(select [dept],[name],[age]
from #TableA
union
select [dept],[name],[age]
from #TableB) as T1
where not exists
(select [dept],[name],[age]
from #TableC where name=t1.name
union
select [dept],[name],[age]
from #TableD where name=T1.name
)
DROP TABLE #TableA,#TableB,#TableC,#TableD;
Cheers!
January 22, 2016 at 5:49 pm
I'm not entirely sure of your exact requirements, but if you want to union together a set of tables and select everything that isn't in another set of tables, you might be better off with an approach like this. I find it simpler and, which I don't have sample data to test against, you might get pretty good performance out of it as well.
WITH cteInclude AS (
SELECT dept, name, age
FROM #TableA
UNION ALL
SELECT dept, name, age
FROM #TableB
),
cteExclude AS (
SELECT dept, name, age
FROM #TableC
UNION ALL
SELECT dept, name, age
FROM #TableD
)
SELECT dept, name, age
FROM cteInclude
EXCEPT
SELECT dept, name, age
FROM cteExclude;
I hope this helps.
January 22, 2016 at 6:23 pm
Thank You Jacob - Really appreciate it. I'll make sure to give proper sample dummy next time. -- Also thanks Ed!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply