Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Compare two tables

3 posts in General Discussion Last posting was on 2010-12-07 21:04:38.0Z
Manuel Espinoza <jmespinoza_no_spam Posted on 2010-12-01 15:58:25.0Z
From: Manuel Espinoza <jmespinoza_no_spam@seicom_dot_com_dot_mx>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; es-ES; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Compare two tables
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cf670a1$1@forums-1-dub>
Date: 1 Dec 2010 07:58:25 -0800
X-Trace: forums-1-dub 1291219105 10.22.241.152 (1 Dec 2010 07:58:25 -0800)
X-Original-Trace: 1 Dec 2010 07:58:25 -0800, vip152.sybase.com
Lines: 22
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29749
Article PK: 78978

hi,

Sorry, I'm stuck on this...

I need to know which rows are in A table and not in B table(assuming A
and B are identical)

I know the ideal query would be

Select *
from A
Where A.id not in(Select B.id from B )


But, this tables have 3 columns as PK( lets say A.country, A.state, A.city)

How could I accomplish this with a query?

BTW, I'm on ASE 15.0.3


Thanks in advance!


Luc Van der Veurst Posted on 2010-12-01 16:03:49.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4cf670a1$1@forums-1-dub>
Subject: Re: Compare two tables
Lines: 34
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-Forwarded: by - (DeleGate/5.8.7)
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cf671e5$1@forums-1-dub>
Date: 1 Dec 2010 08:03:49 -0800
X-Trace: forums-1-dub 1291219429 10.22.241.152 (1 Dec 2010 08:03:49 -0800)
X-Original-Trace: 1 Dec 2010 08:03:49 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29750
Article PK: 78979

select *
from A
where not exists (select * from B
where A.country = B.country and A.state = B.state
and A.city = B.city)

"Manuel Espinoza" <jmespinoza_no_spam@seicom_dot_com_dot_mx> wrote in
message news:4cf670a1$1@forums-1-dub...
> hi,
>
> Sorry, I'm stuck on this...
>
> I need to know which rows are in A table and not in B table(assuming A and
> B are identical)
>
> I know the ideal query would be
>
> Select *
> from A
> Where A.id not in(Select B.id from B )
>
>
> But, this tables have 3 columns as PK( lets say A.country, A.state,
> A.city)
>
> How could I accomplish this with a query?
>
> BTW, I'm on ASE 15.0.3
>
>
> Thanks in advance!


"Paul Horan[Sybase]" Posted on 2010-12-07 21:04:38.0Z
From: "Paul Horan[Sybase]" <phoran AT sybase DOT com>
Newsgroups: sybase.public.ase.general
References: <4cf670a1$1@forums-1-dub> <4cf671e5$1@forums-1-dub>
Subject: Re: Compare two tables
Lines: 47
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5994
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cfea166$1@forums-1-dub>
Date: 7 Dec 2010 13:04:38 -0800
X-Trace: forums-1-dub 1291755878 10.22.241.152 (7 Dec 2010 13:04:38 -0800)
X-Original-Trace: 7 Dec 2010 13:04:38 -0800, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29770
Article PK: 78999

ASE 15.0 doesn't implement the MINUS operator... With that, it's as simple
as:
Select country, state, city from A
MINUS
Select country, state, city from B ;

Paul Horan[Sybase]
http://paulhoran.ulitzer.com

"Luc Van der Veurst" <dba_azvub@hotmail.com> wrote in message
news:4cf671e5$1@forums-1-dub...
> select *
> from A
> where not exists (select * from B
> where A.country = B.country and A.state =
> B.state and A.city = B.city)
>
>
> "Manuel Espinoza" <jmespinoza_no_spam@seicom_dot_com_dot_mx> wrote in
> message news:4cf670a1$1@forums-1-dub...
>> hi,
>>
>> Sorry, I'm stuck on this...
>>
>> I need to know which rows are in A table and not in B table(assuming A
>> and B are identical)
>>
>> I know the ideal query would be
>>
>> Select *
>> from A
>> Where A.id not in(Select B.id from B )
>>
>>
>> But, this tables have 3 columns as PK( lets say A.country, A.state,
>> A.city)
>>
>> How could I accomplish this with a query?
>>
>> BTW, I'm on ASE 15.0.3
>>
>>
>> Thanks in advance!
>
>