Posted by Hugo Kornelis on 12/04/07 20:39

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

>On Dec 4, 9:15 am, Jeff Kish <kishjj...@charter.net> 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

• Next in forum: Re: SELECT * FROM "multiple_tables" WHERE "field_(in_those_tables)_have_the_same_paricular_value" ?
• Prev in forum: Re: Automatically Archiving a Large Table
• Thread view: Re: multi column sub select equivalent for sql server 2005

[Reply to this message]