multi column sub select equivalent for sql server 2005

[Jeff Kish]

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

select table1.col1,tab le1.col2,table2 .col3,table4.co l4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
(table1.col1,ta ble1.col2) not in
select table2.col4,tab le2.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

[Erland Sommarskog]

Re: multi column sub select equivalent for sql server 2005

Jeff Kish (kishjjrjj@char ter.net) writes:

Can anyone tell me how to do this in sql server?
I am currently doing this query in oracle:
>
select table1.col1,tab le1.col2,table2 .col3,table4.co l4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
(table1.col1,ta ble1.col2) not in
select table2.col4,tab le2.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.

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[FunBoy]

Re: multi column sub select equivalent for sql server 2005

On Dec 4, 9:15 am, Jeff Kish <kishjj...@char ter.netwrote:

Can anyone tell me how to do this in sql server?
I am currently doing this query in oracle:
>
select table1.col1,tab le1.col2,table2 .col3,table4.co l4
where table1.col1 = table2.col3 and
table2.col3 = table4.col5 and
(table1.col1,ta ble1.col2) not in
select table2.col4,tab le2.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

It can be done slightly different way,


select table1.col1,tab le1.col2,table2 .col3,table4.co l4
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.col 4 as varchar(20)) + '-' + cast(table2.col 5 as
varchar(20)) from table2)

Regards
Monojit

[Jeff Kish]

Re: multi column sub select equivalent for sql server 2005

On Tue, 4 Dec 2007 09:25:48 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:

>Jeff Kish (kishjjrjj@char ter.net) writes:

>Can anyone tell me how to do this in sql server?
>I am currently doing this query in oracle:
>>
>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
>where table1.col1 = table2.col3 and
>table2.col3 = table4.col5 and
>(table1.col1,t able1.col2) not in
>select table2.col4,tab le2.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.

>
>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)

thanks much. I'll test things out.
Jeff

[Jeff Kish]

Re: multi column sub select equivalent for sql server 2005

On Tue, 4 Dec 2007 01:36:01 -0800 (PST), FunBoy
<monojitaich@gm ail.comwrote:

>On Dec 4, 9:15 am, Jeff Kish <kishjj...@char ter.netwrote:

>Can anyone tell me how to do this in sql server?
>I am currently doing this query in oracle:
>>
>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
>where table1.col1 = table2.col3 and
>table2.col3 = table4.col5 and
>(table1.col1,t able1.col2) not in
>select table2.col4,tab le2.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

>
>
>It can be done slightly different way,
>
>
>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
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.col 4 as varchar(20)) + '-' + cast(table2.col 5 as
>varchar(20)) from table2)
>
>Regards
Monojit

thanks. I take it this '-' stops the data from accidentally
matching?
Jeff

[Tom van Stiphout]

Re: multi column sub select equivalent for sql server 2005

On Tue, 04 Dec 2007 07:32:33 -0500, Jeff Kish <kishjjrjj@char ter.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 Tue, 4 Dec 2007 01:36:01 -0800 (PST), FunBoy
><monojitaich@g mail.comwrote:
>

>>On Dec 4, 9:15 am, Jeff Kish <kishjj...@char ter.netwrote:

>>Can anyone tell me how to do this in sql server?
>>I am currently doing this query in oracle:
>>>
>>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
>>where table1.col1 = table2.col3 and
>>table2.col3 = table4.col5 and
>>(table1.col1, table1.col2) not in
>>select table2.col4,tab le2.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

>>
>>
>>It can be done slightly different way,
>>
>>
>>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
>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.col 4 as varchar(20)) + '-' + cast(table2.col 5 as
>>varchar(20) ) from table2)
>>
>>Regards
>Monojit

>thanks. I take it this '-' stops the data from accidentally
>matching?
>Jeff

[kishjeff]

Re: multi column sub select equivalent for sql server 2005

On Dec 4, 8:56 am, Tom van Stiphout <no.spam.tom7.. .@cox.netwrote:

On Tue, 04 Dec 2007 07:32:33 -0500, Jeff Kish <kishjj...@char ter.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 Tue, 4 Dec 2007 01:36:01 -0800 (PST), FunBoy
<monojita...@gm ail.comwrote:

>

>On Dec 4, 9:15 am, Jeff Kish <kishjj...@char ter.netwrote:
>Can anyone tell me how to do this in sql server?
>I am currently doing this query in oracle:

>

>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
>where table1.col1 = table2.col3 and
>table2.col3 = table4.col5 and
>(table1.col1,t able1.col2) not in
>select table2.col4,tab le2.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

>

>It can be done slightly different way,

>

>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
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.col 4 as varchar(20)) + '-' + cast(table2.col 5 as
>varchar(20)) from table2)

>

>Regards
Monojit

thanks. I take it this '-' stops the data from accidentally
matching?
Jeff- Hide quoted text -

>
- Show quoted text -

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

[Hugo Kornelis]

Re: multi column sub select equivalent for sql server 2005

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

>On Dec 4, 9:15 am, Jeff Kish <kishjj...@char ter.netwrote:

>Can anyone tell me how to do this in sql server?
>I am currently doing this query in oracle:
>>
>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
>where table1.col1 = table2.col3 and
>table2.col3 = table4.col5 and
>(table1.col1,t able1.col2) not in
>select table2.col4,tab le2.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

>
>
>It can be done slightly different way,
>
>
>select table1.col1,tab le1.col2,table2 .col3,table4.co l4
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.col 4 as varchar(20)) + '-' + cast(table2.col 5 as
>varchar(20)) from table2)

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

[Erland Sommarskog]

Re: multi column sub select equivalent for sql server 2005

kishjeff (kishjjrjj@char ter.net) writes:

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?

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Tom van Stiphout]

Re: multi column sub select equivalent for sql server 2005

On Tue, 4 Dec 2007 22:29:35 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:

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

>kishjeff (kishjjrjj@char ter.net) writes:

>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?

>
>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.

[Jeff Kish]

Re: multi column sub select equivalent for sql server 2005

On Tue, 4 Dec 2007 22:29:35 +0000 (UTC), Erland Sommarskog
<esquel@sommars kog.sewrote:

>kishjeff (kishjjrjj@char ter.net) writes:

>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?

>
>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.

well NOT EXISTS it is then!
thanks Erland! (and everyone) I appreciate all the education.

Jeff