July 28, 2018 at 5:08 pm
Hello,
I have this query,
select *
from
(select [KD]
from [dbo].[Tab1]
union
select [KD]
from [dbo].[Tab2]
where [KD] like '%abc%' or [KD] like '%def%') as C
where not exists
(select [KD]
from [dbo].[Tab3] as T
where c.[KD]=t.[KD]
)
I want the output to be only where it combines tabl 1 and 2 via the union, and compares those results against tab 3, and only displays results where this condition is met, where [KD] like '%abc%' or [KD] like '%def%') from the union is not in tab3.
However it is not delivering the expected result. I think there is a problem with scope. How can I rewrite, where it works?
Thanks
July 28, 2018 at 7:50 pm
Your parentheses look wrong.
SELECT x.*
FROM
( SELECT a
FROM table1
WHERE...
UNION ALL
SELECT b
FROM table2
WHERE... ) x
WHERE NOT EXISTS (SELECT 1 FROM Table3 WHERE table3.Column = x.A)
?
July 28, 2018 at 8:51 pm
Ok let me try, hmm still missing some data. Here's the updated version I have based on your feedback
select *
from
(select [KD]
from [dbo].[Tab1]
where [KD] like '%abc%' or [KD] like '%def%'
union
select [KD]
from [dbo].[Tab2]
where [KD] like '%abc%' or [KD] like '%def%') as C
where not exists
(select [KD]
from [dbo].[Tab3] as T
where c.[KD]=t.[KD]
)
July 28, 2018 at 10:29 pm
Without sample data (CREATE TABLE and INSERT scripts), there's no way for us to test your query for you. So all we can give you is reasonably educated guesses. If you can provide data, you have a good chance of getting a tested solution.
Read Jeff's article Forum Etiquette: How to post data/code on a forum to get the best help... it has all the information you need for us to help you.
July 29, 2018 at 3:45 am
select kd
from
(
select kd from table1
intersect select kd from table2
except select kd from table3
) U
where U.kd like '%abc%' or U.kd like '%def%'
July 31, 2018 at 12:09 pm
Hey guys. thanks for your help. I went back and re checked and I had overlooked a result. Your help is really appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply