Hello

How to match two columns in a table with a column in another table. I
am using Sybase server. It has two tables - Customer and Country

Customer table stores customer information and country table is just a
matching table.

Customer Table has:
customer_id
firstname
lastname
place_of_born (i.e country_code)
retirement_country (i.e country_code)

Country Table has:
country_code (e.g. Aus)
country_description (e.g Australia)

My idea is showing the country_description of place_of_born and
retirement_country instead of country_code after sql query. My expect
result is

1234
John
Cornor
Australia
America

instead of

1234
John
Cornor
Aus
US

Pleaes give me some suggestion on writing the SQL statement.

Thanks

On Sun, 31 Jul 2005 08:54:52 -0700, BJ wrote:

> Hello
>
> How to match two columns in a table with a column in another table. I am
> using Sybase server. It has two tables - Customer and Country
>
> Customer table stores customer information and country table is just a
> matching table.
>
> Customer Table has:
> customer_id
> firstname
> lastname
> place_of_born (i.e country_code)
> retirement_country (i.e country_code)
>
> Country Table has:
> country_code (e.g. Aus)
> country_description (e.g Australia)
>
> My idea is showing the country_description of place_of_born and
> retirement_country instead of country_code after sql query. My expect
> result is

It's a simple join:


select cs.firstname
, cs.lastname
, c1.country_description
, c2.country_description
from customer cs
, country c1
, country c2
where cs.place_born = c1.country_code
and cs.retirement_country = c2.country_code

should return the information you want.

Michael
--
Michael Peppler [TeamSybase] mpeppler@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html

I did it.

thanks Michael

What you need is a 'join' -- like this:

select customer_id, firstname, lastname, born=c1.country_description,
retired=c2.country_description
from Customer c, Country c1, Country c2
where c.place_of_born = c1.country_code
and c.retirement_country = c2.country_code


I recommend reading some basic documentation about SQL since 'joins' are a
rather basic element of SQL (for example, go to
http://sybooks.sybase.com/as.html and pick up a copy of the 'Transact SQL
USer's Guide').

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase Replication Server Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"BJ" <bonnie.tangyn@gmail.com> wrote in message
news:1122825292.490050.28500@f14g2000cwb.googlegro ups.com...
> Hello
>
> How to match two columns in a table with a column in another table. I
> am using Sybase server. It has two tables - Customer and Country
>
> Customer table stores customer information and country table is just a
> matching table.
>
> Customer Table has:
> customer_id
> firstname
> lastname
> place_of_born (i.e country_code)
> retirement_country (i.e country_code)
>
> Country Table has:
> country_code (e.g. Aus)
> country_description (e.g Australia)
>
> My idea is showing the country_description of place_of_born and
> retirement_country instead of country_code after sql query. My expect
> result is
>
> 1234
> John
> Cornor
> Australia
> America
>
> instead of
>
> 1234
> John
> Cornor
> Aus
> US
>
> Pleaes give me some suggestion on writing the SQL statement.
>
> Thanks
>