July 16, 2014 at 11:15 am
Can't seem to make this SQL query work! I have searched for the answer to this and although some posts come close, they just miss the mark.
Given one table, Table1, with columns Key1 (int), Key2 (int), and Type (varchar)...
I would like to get the rows where Type is equal to 'TypeA' and Key2 is Null that do NOT have a corresponding row in the table where Type is equal to 'TypeB' and Key2 is equal to Key1 from another row
So, given the data
**KEY1** **Key2** **Type**
1 NULL TypeA
2 5 TypeA
3 1 TypeB
4 NULL TypeA
5 NULL TypeB
I would like to return only the row where Key1 = 4 because that row meets the criteria of Type='TypeA'/Key2=NULL and does not have a corresponding row with Type='TypeB'/Key1=Key2 from another row.
I have tried this and it doesn't work...
SELECT t1.Key1, t1.Key2, t1.Type
FROM Table1 t1
WHERE t1.Key2 IS NULL
AND t1.Type LIKE 'TypeA'
AND t1.Key1 NOT IN
(SELECT Key1
FROM Table1 t2
WHERE t1.Key1 = t2.Key2
AND t1.Key1 <> t2.Key1
AND t2.Type LIKE 'TypeB')
July 16, 2014 at 11:54 am
Something like this?
select t1.Key1
,t1.Key2
,t1.Type
from Table1 t1
where t1.Key2 is null
and t1.Type = 'TypeA'
and not exists (
select *
from table1 t
where t.type = 'TypeB'
and t.Key2 = t1.Key1
)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 16, 2014 at 5:49 pm
Thanks for the great solution!
Maybe you could help me out with this same scenario but in reverse??? LOL
http://www.sqlservercentral.com/Forums/Topic1593333-3077-1.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply