T-SQL Query Question

T-SQL Query Question

Post by Kurt Moska » Wed, 31 May 2000 04:00:00



I created two tables using the following commands

create table #test1 (col1 int)
create table #test2 (col2 int)

insert into #test1 values (1)
insert into #test1 values (2)
insert into #test1 values (3)
insert into #test1 values (4)
insert into #test1 values (5)
insert into #test1 values (6)
insert into #test1 values (7)
insert into #test1 values (8)

insert into #test2 values (1)
insert into #test2 values (2)
insert into #test2 values (3)
insert into #test2 values (4)
insert into #test2 values (5)
insert into #test2 values (6)

What I'm looking for is a select statement that will return all the rows in #test1 that are not in #test2.
The result set should return only the values 7 and 8 (these are the only rows not in #test2).

The only catch is I must use a join and can't use the "in" statement. So even thought this works "select * from #test1 where col1 not in (select col2 from #test2)" it's not valid because it's using a subquery.

Any idea??

Thanks

Kurt Moskal

 
 
 

T-SQL Query Question

Post by Keith Kratochvi » Wed, 31 May 2000 04:00:00


SELECT A.*
FROM #test1 A
LEFT OUTER JOIN #test2 B ON A.Col1 = B.Col2
WHERE B.Col2 IS NULL

Keith


  I created two tables using the following commands

  create table #test1 (col1 int)
  create table #test2 (col2 int)

  insert into #test1 values (1)
  insert into #test1 values (2)
  insert into #test1 values (3)
  insert into #test1 values (4)
  insert into #test1 values (5)
  insert into #test1 values (6)
  insert into #test1 values (7)
  insert into #test1 values (8)

  insert into #test2 values (1)
  insert into #test2 values (2)
  insert into #test2 values (3)
  insert into #test2 values (4)
  insert into #test2 values (5)
  insert into #test2 values (6)

  What I'm looking for is a select statement that will return all the rows in #test1 that are not in #test2.
  The result set should return only the values 7 and 8 (these are the only rows not in #test2).

  The only catch is I must use a join and can't use the "in" statement. So even thought this works "select * from #test1 where col1 not in (select col2 from #test2)" it's not valid because it's using a subquery.

  Any idea??

  Thanks

  Kurt Moskal


 
 
 

T-SQL Query Question

Post by Kurt Moska » Wed, 31 May 2000 04:00:00


Thanks,

It works just like I wanted. I really thought I tried this before with not luck.

  SELECT A.*
  FROM #test1 A
  LEFT OUTER JOIN #test2 B ON A.Col1 = B.Col2
  WHERE B.Col2 IS NULL

  Keith


    I created two tables using the following commands

    create table #test1 (col1 int)
    create table #test2 (col2 int)

    insert into #test1 values (1)
    insert into #test1 values (2)
    insert into #test1 values (3)
    insert into #test1 values (4)
    insert into #test1 values (5)
    insert into #test1 values (6)
    insert into #test1 values (7)
    insert into #test1 values (8)

    insert into #test2 values (1)
    insert into #test2 values (2)
    insert into #test2 values (3)
    insert into #test2 values (4)
    insert into #test2 values (5)
    insert into #test2 values (6)

    What I'm looking for is a select statement that will return all the rows in #test1 that are not in #test2.
    The result set should return only the values 7 and 8 (these are the only rows not in #test2).

    The only catch is I must use a join and can't use the "in" statement. So even thought this works "select * from #test1 where col1 not in (select col2 from #test2)" it's not valid because it's using a subquery.

    Any idea??

    Thanks

    Kurt Moskal

 
 
 

T-SQL Query Question

Post by Roy Harve » Wed, 31 May 2000 04:00:00


Kurt,

An alternate approach:

SELECT *
  FROM #test1
 WHERE NOT EXISTS(select * from #test2
                   where #test1.col1 = #test2.col2)

Roy

 
 
 

T-SQL Query Question

Post by vnc.. » Fri, 02 Jun 2000 04:00:00


select col1 from #test1 full outer join #test2  on col1=col2 where
col2 is null



> This is a multi-part message in MIME format.

> ------=_NextPart_000_000E_01BFCA50.705C6100
> Content-Type: text/plain;
>    charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable

> I created two tables using the following commands

> create table #test1 (col1 int)
> create table #test2 (col2 int)

> insert into #test1 values (1)
> insert into #test1 values (2)
> insert into #test1 values (3)
> insert into #test1 values (4)
> insert into #test1 values (5)
> insert into #test1 values (6)
> insert into #test1 values (7)
> insert into #test1 values (8)

> insert into #test2 values (1)
> insert into #test2 values (2)
> insert into #test2 values (3)
> insert into #test2 values (4)
> insert into #test2 values (5)
> insert into #test2 values (6)

> What I'm looking for is a select statement that will return all the
rows =
> in #test1 that are not in #test2.=20
> The result set should return only the values 7 and 8 (these are the
only =
> rows not in #test2).

> The only catch is I must use a join and can't use the "in" statement.
So =
> even thought this works "select * from #test1 where col1 not in
(select =
> col2 from #test2)" it's not valid because it's using a subquery.

> Any idea??

> Thanks

> Kurt Moskal

> ------=_NextPart_000_000E_01BFCA50.705C6100
> Content-Type: text/html;
>    charset="iso-8859-1"
> Content-Transfer-Encoding: quoted-printable

> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META content=3D"text/html; charset=3Diso-8859-1" =
> http-equiv=3DContent-Type>
> <META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT face=3DArial size=3D2>I created two tables using the =
> following=20
> commands</FONT></DIV>
> <DIV>&nbsp;</DIV>
> <DIV><FONT face=3DArial size=3D2>create table #test1 (col1 =
> int)<BR>create table=20
> #test2 (col2 int)</FONT></DIV>
> <DIV>&nbsp;</DIV>
> <DIV><FONT face=3DArial size=3D2>insert into #test1 values (1)
<BR>insert =
> into #test1=20
> values (2)<BR>insert into #test1 values (3)<BR>insert into #test1
values =

> (4)<BR>insert into #test1 values (5)<BR>insert into #test1 values =
> (6)<BR>insert=20
> into #test1 values (7)</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>insert into #test1 values =
> (8)</FONT></DIV>
> <DIV>&nbsp;</DIV>
> <DIV><FONT face=3DArial size=3D2>insert into #test2 values (1)
<BR>insert =
> into #test2=20
> values (2)<BR>insert into #test2 values (3)<BR>insert into #test2
values =

> (4)<BR>insert into #test2 values (5)<BR>insert into #test2 values=20
> (6)<BR></FONT></DIV>
> <DIV><FONT face=3DArial size=3D2>
> <DIV>
> <DIV>
> <DIV>
> <DIV>
> <DIV>
> <DIV>
> <DIV><FONT face=3DArial size=3D2>
> <DIV>
> <DIV>
> <DIV>
> <DIV>
> <DIV>
> <DIV>&nbsp;</DIV>
> <DIV>What I'm looking for is a select statement that will return all
the =
> rows=20
> in&nbsp;#test1 that are not in #test2.=20
> <DIV>The result set should return only the values 7 and 8 (these are
the =
> only=20
> rows not in #test2).</DIV>
> <DIV>&nbsp;</DIV>The only catch is I must use a join and&nbsp;can't
use =
> the "in"=20
> statement. So even thought this works "select * from #test1 where
col1 =
> not in=20
> (select col2 from #test2)" it's not valid because it's using a =
> subquery.</DIV>
> <DIV>&nbsp;</DIV>
> <DIV>Any idea??</DIV>
> <DIV>&nbsp;</DIV>
> <DIV>Thanks</DIV>
> <DIV>&nbsp;</DIV>
> <DIV>Kurt Moskal</DIV>
> <DIV><A =


Quote:> <DIV>&nbsp;</DIV>
> <DIV>&nbsp;</DIV>

<DIV>&nbsp;</DIV></FONT></DIV></DIV></DIV></DIV></DIV></DIV></DIV></DIV>
<=

Quote:> /DIV></DIV></DIV></DIV></FONT></DIV></BODY></HTML>

> ------=_NextPart_000_000E_01BFCA50.705C6100--

Sent via Deja.com http://www.deja.com/
Before you buy.