multi column sub select equivalent for sql server 2005

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

Re: multi column sub select equivalent for sql server 2005

Jeff Kish (kishjjrjj@charter.net) writes:
> 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.

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: multi column sub select equivalent for sql server 2005

On Dec 4, 9:15 am, Jeff Kish wrote:
> 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


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

Re: multi column sub select equivalent for sql server 2005

On Tue, 4 Dec 2007 09:25:48 +0000 (UTC), Erland Sommarskog
wrote:

>Jeff Kish (kishjjrjj@charter.net) writes:
>> 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.
>
>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

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 wrote:
>> 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
>
>
>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
thanks. I take it this '-' stops the data from accidentally
matching?
Jeff

Re: multi column sub select equivalent for sql server 2005

On Tue, 04 Dec 2007 07:32:33 -0500, Jeff Kish
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
> wrote:
>
>>On Dec 4, 9:15 am, Jeff Kish wrote:
>>> 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
>>
>>
>>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
>thanks. I take it this '-' stops the data from accidentally
>matching?
>Jeff

Re: multi column sub select equivalent for sql server 2005

On Dec 4, 8:56 am, Tom van Stiphout wrote:
> On Tue, 04 Dec 2007 07:32:33 -0500, Jeff Kish
> 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
> > wrote:
>
> >>On Dec 4, 9:15 am, Jeff Kish wrote:
> >>> 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
>
> >>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
> >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

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

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

Re: multi column sub select equivalent for sql server 2005

kishjeff (kishjjrjj@charter.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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: multi column sub select equivalent for sql server 2005

On Tue, 4 Dec 2007 22:29:35 +0000 (UTC), Erland Sommarskog
wrote:

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


>kishjeff (kishjjrjj@charter.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.

Re: multi column sub select equivalent for sql server 2005

On Tue, 4 Dec 2007 22:29:35 +0000 (UTC), Erland Sommarskog
wrote:

>kishjeff (kishjjrjj@charter.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