Trying to join two columns in subquery

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to join two columns in subquery

Author

Topic

Trojka
Starting Member

10 Posts

Posted - 2007-06-21 : 04:14:27

After working with Oracle (can I say that out loud here?

) databases for a couple of years, our company recently switched to SQL2005. So far I really like it, and the switch has been relatively easy for me. Except for one thing...

I'm trying to find out how to join a subquery on multiple columns. In Oracle it would like this:

select * from #temp1 where (id_1, id_2) not in (select id_1, id_2 from #temp2)

But this doesn't work in SQL-T. I've temporarily solved it by doing this:

select * from #temp1 where id_1+id_2 not in (select id_1+id_2 from #temp2)

I don't like concat solutions for joins though (it always feels 'unsafe' to me). Plus, I can't believe there's no way to solve this.

I've searched the FAQ and BOL but I just can't seem to find the solution. Please help. I feel stupid.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 04:26:41

quote:
can I say that out loud here?

Yes. Don't worry about it.

Does Oracle allows you to do this ?

quote:
select * from #temp1 where (id_1, id_2) not in (select id_1, id_2 from #temp2)

in MSSQL, we use LEFT JOIN

SELECT 	* 
FROM 	#temp1 t1 LEFT JOIN #temp2 t2
	ON	t1.id_1	= t2.id_1
	AND	t1.id_2 = t2.id_2
WHERE	t2.id_1	 IS  NULL

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-21 : 04:28:56

You can either use LEFT JOIN or NOT EXISTS.

Select * from #Temp1 t1 
where not exists (select * from #temp2 t2 where t2.id_1 = t1.id_1 and t2.id_2 = t1.id_2)

Select t1.*
from #Temp1 t1
LEFT JOIN #temp2 t2
on t1.id_1 = t2.id_1 and t1.id_2 = t2.id_2
Where t2.id_1 IS NULL and t2.id_2 IS NULL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-21 : 04:30:26

KH,

Seems like you also incorporated macro for color-coding in your favourite editor, eh?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 04:32:01

quote:
Originally posted by harsh_athalye

KH,

Seems like you also incorporated macro for color-coding in your favourite editor, eh?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Yes.

it actually make the codes easier to read

Trojka
Starting Member

10 Posts

Posted - 2007-06-21 : 04:45:48

Heh, I've tried the left join option before but forgot the 't2.id_1 IS NULL'.

Makes perfect sense now.

Did I mention I love temporary tables yet? One of the best things in MSSQL, so far.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources