select statement

[Pat]

Hi,

I am trying to select all the records in table A which is not in table B.
It will be simple if we have only one joining field.
Select * from A where A.col1 not in (select B.col1 from B where B...).

But my joining field are 2 columns instead, I tried
Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
B WHERE) seems illegal.

Has anybody done this before?

Thanks
Pat

[David Satz]

you can do:

Select *
from A
where not exists ( select 1
from B
where A.col1 = B.col1 )

Select *
from A
left outer join B ON A.col1 = B.col1
where B.col1 is null

--
HTH,
David Satz
Principal Web Engineer
Hyperion Solutions
{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS }
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------
"Pat" <pwang@uclink.berkeley.edu> wrote in message
news:3c8f7bed$1@10.1.10.29...
>
> Hi,
>
> I am trying to select all the records in table A which is not in table B.
> It will be simple if we have only one joining field.
> Select * from A where A.col1 not in (select B.col1 from B where B...).
>
> But my joining field are 2 columns instead, I tried
> Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
> also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
> B WHERE) seems illegal.
>
> Has anybody done this before?
>
> Thanks
> Pat

[Pat]

Thanks David:

I tried no 1 works, no 2 not.

Pat

"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>you can do:
>
>Select *
>from A
>where not exists ( select 1
> from B
> where A.col1 = B.col1 )
>
>Select *
>from A
>left outer join B ON A.col1 = B.col1
>where B.col1 is null
>
>--
>HTH,
>David Satz
>Principal Web Engineer
>Hyperion Solutions
>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } { VSS
}
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>"Pat" <pwang@uclink.berkeley.edu> wrote in message
>news:3c8f7bed$1@10.1.10.29...
>>
>> Hi,
>>
>> I am trying to select all the records in table A which is not in table
B.
>> It will be simple if we have only one joining field.
>> Select * from A where A.col1 not in (select B.col1 from B where B...).
>>
>> But my joining field are 2 columns instead, I tried
>> Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
>> also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
>> B WHERE) seems illegal.
>>
>> Has anybody done this before?
>>
>> Thanks
>> Pat
>
>

[Vishwas]

Hi , Pat I Beleive this is quite Correct
Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
>>> B WHERE)

This is what ur trying instead u try

select * from a where (a.col,a.col2) in (select b.col1,b.col2 from .....

I hv done nothing , just a Bracket is there...try it.

Regards
Vishwas




"Pat" <pwang@uclink.berkeley.edu> wrote:
>
>Thanks David:
>
>I tried no 1 works, no 2 not.
>
>Pat
>
>"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>>you can do:
>>
>>Select *
>>from A
>>where not exists ( select 1
>> from B
>> where A.col1 = B.col1 )
>>
>>Select *
>>from A
>>left outer join B ON A.col1 = B.col1
>>where B.col1 is null
>>
>>--
>>HTH,
>>David Satz
>>Principal Web Engineer
>>Hyperion Solutions
>>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
VSS
>}
>>(Please reply to group only - emails answered rarely)
>>-----------------------------------------------------------------
>>"Pat" <pwang@uclink.berkeley.edu> wrote in message
>>news:3c8f7bed$1@10.1.10.29...
>>>
>>> Hi,
>>>
>>> I am trying to select all the records in table A which is not in table
>B.
>>> It will be simple if we have only one joining field.
>>> Select * from A where A.col1 not in (select B.col1 from B where B...).
>>>
>>> But my joining field are 2 columns instead, I tried
>>> Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
>>> also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
>>> B WHERE) seems illegal.
>>>
>>> Has anybody done this before?
>>>
>>> Thanks
>>> Pat
>>
>>
>

[Kudel]

This is what you must do..

select *
from a
where a.col1 not in
(
select b.col1
from b
where b.col2 not like a.col2
)


..if you want it to work

-Kudel


"Vishwas" <vbansal@ecomserver.com> wrote:
>
>Hi , Pat I Beleive this is quite Correct
>Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
>>>> B WHERE)
>
> This is what ur trying instead u try
>
>select * from a where (a.col,a.col2) in (select b.col1,b.col2 from .....
>
>I hv done nothing , just a Bracket is there...try it.
>
>Regards
>Vishwas
>
>
>
>
>"Pat" <pwang@uclink.berkeley.edu> wrote:
>>
>>Thanks David:
>>
>>I tried no 1 works, no 2 not.
>>
>>Pat
>>
>>"David Satz" <davidNOSPAMsatz@yahoo.com> wrote:
>>>you can do:
>>>
>>>Select *
>>>from A
>>>where not exists ( select 1
>>> from B
>>> where A.col1 = B.col1 )
>>>
>>>Select *
>>>from A
>>>left outer join B ON A.col1 = B.col1
>>>where B.col1 is null
>>>
>>>--
>>>HTH,
>>>David Satz
>>>Principal Web Engineer
>>>Hyperion Solutions
>>>{ SQL Server 2000 SP1/7.0 SP3/6.5 SP5a } { Cold Fusion 5/4.5.1 SP2 } {
>VSS
>>}
>>>(Please reply to group only - emails answered rarely)
>>>-----------------------------------------------------------------
>>>"Pat" <pwang@uclink.berkeley.edu> wrote in message
>>>news:3c8f7bed$1@10.1.10.29...
>>>>
>>>> Hi,
>>>>
>>>> I am trying to select all the records in table A which is not in table
>>B.
>>>> It will be simple if we have only one joining field.
>>>> Select * from A where A.col1 not in (select B.col1 from B where B...).
>>>>
>>>> But my joining field are 2 columns instead, I tried
>>>> Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
>>>> also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2
from
>>>> B WHERE) seems illegal.
>>>>
>>>> Has anybody done this before?
>>>>
>>>> Thanks
>>>> Pat
>>>
>>>
>>
>

[Kudel]

A "not" to much in the last one..

select *
from a
where a.col1 not in
(
select b.col1
from b
where b.col2 like a.col2
)


-Kudel

[russel]

how about this?

select * from A
left join B
on A.col1=B.col1 and A.col2=B.col2
Where B.col3 is null


"Pat" <pwang@uclink.berkeley.edu> wrote:
>
>Hi,
>
>I am trying to select all the records in table A which is not in table B.
>It will be simple if we have only one joining field.
>Select * from A where A.col1 not in (select B.col1 from B where B...).
>
>But my joining field are 2 columns instead, I tried
>Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
>also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
>B WHERE) seems illegal.
>
>Has anybody done this before?
>
>Thanks
>Pat

[Steve]

Try concatenating two fields in the tables.

Example

Table A Table B
Col_1 Col_2 Col_3 Col_1 Col_2 Col_3
A 1 A1 A 2 A2
B 2 B2 B 2 B2

Then it's simple to compare:
select *
from A
where A.Col_3 != B.Col_3 and
A.Col_1 = B.Col_1
It should give you your exceptions

Hope it helps.


"Pat" <pwang@uclink.berkeley.edu> wrote:
>
>Hi,
>
>I am trying to select all the records in table A which is not in table B.
>It will be simple if we have only one joining field.
>Select * from A where A.col1 not in (select B.col1 from B where B...).
>
>But my joining field are 2 columns instead, I tried
>Select * from A, B where A.col1!=B.col1 or A.col2!=B.col2, didnot work.
>also Select * from A where A.col1, A.col2 in (Select B.col1, B.col2 from
>B WHERE) seems illegal.
>
>Has anybody done this before?
>
>Thanks
>Pat