Can anyone tell me how to do this in sql server?
I am currently doing this query in oracle:

select table1.col1,table1.col2,table2.col3,table4.col4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
(table1.col1,table1.col2) not in
select table2.col4,table2.col5 from table2


it is the where two column values from any row are not found in any
row in table2 part that I can't figure out.



thanks
Jeff

Jeff Kish (kishjjrjj (AT) charter (DOT) net) writes:
Row constructors is part of the ANSI Stanard, but not implemented in
SQL Server. So it's only to chalk one up for Oracle there.

Fortunately, in this situation you could just as well use NOT EXISTS:

select ...
from table1 t1
where not exists (select *
from table2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2)



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Dec 4, 9:15 am, Jeff Kish <kishjj... (AT) charter (DOT) net> wrote:

It can be done slightly different way,


select table1.col1,table1.col2,table2.col3,table4.col4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
cast(table1 as varchar(20)) +'-'+cast(col1 as varchar(20)) not in(
select cast(table2.col4 as varchar(20)) + '-' + cast(table2.col5 as
varchar(20)) from table2)

Regards
Monojit

On Tue, 4 Dec 2007 09:25:48 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Jeff

On Tue, 4 Dec 2007 01:36:01 -0800 (PST), FunBoy
<monojitaich (AT) gmail (DOT) com> wrote:

matching?
Jeff

On Tue, 04 Dec 2007 07:32:33 -0500, Jeff Kish <kishjjrjj (AT) charter (DOT) net>
wrote:

It creates a single column with concatenated data. You could use any
concatenator (if that's a word) e.g. verticalbar or tilde that is not
used in the actual data.

-Tom.

On Dec 4, 8:56 am, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote:
I guess that is the 'key', i.e. the character has to be absolutely not
in the data or it has the potential (small but real) to fail, right?

thanks
Jeff

On Tue, 4 Dec 2007 01:36:01 -0800 (PST), FunBoy wrote:

Hi Monojit,

While that may work (provided the '-' character doesn't occur in the
regular data), it will not perform quite as well as the NOT EXISTS
versions posted by Erland. If there are any indexes on the columns,
embedding them in an expression will make it impossible for the DBMS to
use that index for efficient seeking of matching rows.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

kishjeff (kishjjrjj (AT) charter (DOT) net) writes:
The key is that it is a completely unnecessary kludge, when there exists
a perfectly normal solution with NOT EXISTS.

Not that this kludge also prevents efficient use of indexes.

There are cases when concatenating stuff can be a useful trick, but this
is not one of them.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Tue, 4 Dec 2007 22:29:35 +0000 (UTC), Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

I absolutely agree. I was just answering the question.
-Tom.