September 29, 2005 at 1:31 am
Hi All,
Here's a problem I've got.
I've got 2 sub-queries that return 2 columns of data each, the combination of the 2 columns makes a row unique, what I want to do is compare the 2 subqueries and return the rows only in subquery 2 where a value in column b doesn't exist in column b is subquery 1.
SubqueryA will return something like:
t.columnA | t.columnB
---------------------
1 | A
1 | B
1 | C
2 | A
2 | B
2 | C
SubqueryB will return something like:
t.columnA | t.columnB
---------------------
1 | A
1 | B
2 | A
2 | C
The result set should look like:
t.columnA | t.columnB
---------------------
1 | C
2 | B
Essentially something like
Select t.columnA, t.columnB, t1.columnA, t1.ColumnB
from subquery1 t
left outer join
subquery2 t1
on t.columnA = t1.columnA
and t.columnB = t1.columnB
where t1.columnB is null
But it's not working, Any and all help is greatly appreciated
September 29, 2005 at 2:33 am
Can you try this?
Select t.columnA, t.columnB from subquery1 t where not exists (select 1 from subquery2 t1 where t.columnA = t1.columnA
and t.columnB = t1.columnB)
Would you get what you want?
Leo
September 29, 2005 at 2:22 pm
Do a left outer join between b to a on both columns and filter (where ) a.keyfield IS NULL. You only need the columns from b then in your result set.
September 30, 2005 at 4:12 am
Select t.columnA, t.columnB, t1.columnA, t1.ColumnB as nullcolumn
into #temp
from subquery1 t
left outer join
subquery2 t1
on t.columnA = t1.columnA
and t.columnB = t1.columnB
where t1.columnB is null
go
select * from #temp where nullcolumn is null
hope this will work.
September 30, 2005 at 6:49 am
Your query will return the results posted with the data you posted.
What do you mean by not working?
Check what is being returned from the subqueries, if either of them return a null for one of the columns you will get incorrect results
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply