select statement
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
[523 byte] By [
Pat] at [2007-11-9 21:06:49]
# 1 Re: select statement
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
# 2 Re: select statement
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
>
>
Pat at 2007-11-11 23:57:32 >
# 3 Re: select statement
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
>>
>>
>
# 4 Re: select statement
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 at 2007-11-11 23:59:28 >
# 5 Re: select statement
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
Kudel at 2007-11-12 0:00:31 >
# 6 Re: select statement
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
russel at 2007-11-12 0:01:30 >
# 7 Re: select statement
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
Steve at 2007-11-12 0:02:29 >
