Hi Everyone,

i have two tables in the database . One is called address table
and one is adressPhone Table. Below is the sample of those two tables

Address

col1 col2 col3
X 12 13
y 15 19
z 18 10


AddressPhone

Col4 Col5 Col6

13 213-455-9876 1
13 415-564-6546 2
13 543-987-5677 3
19 678-555-2222 1

I have to display something like this

x 12 13 213-455-9876 415-564-6546 543-987-5677

y 15 19 678-555-2222 NULL NULL

So there is one to many relationship between address and
addressPhone table where address.col3 = addressphone.col4

I don't know how to write the query to get the phone numbers that
has he same id in the same row like I displyed above.

I did something like this, but this is not working

select col1,col2,col3, (select col5 from addressPhone where col6=1),
(select col5 from addressPhone where col6=2), (select col5 from
addressPhone where col6=3),
from address table inner join addressPhone
on address.col3=addressphone.col4

above is not working because it is complaining that a subquery
cannot return multiple results. Col6 in the addressphone table is
the phone type ike business phone,
mobile phone or home phone. It is possible that there are two phone
numbers for business phone.

Please let me know how can I write this query.

Any help will be greatly appreciated.

Thanks









Subscribe: sql-general-subscribe (AT) yahoogroups (DOT) com
Unsubscribe: sql-general-unsubscribe (AT) yahoogroups (DOT) com
List owner: sql-general-owner (AT) yahoogroups (DOT) com
URL: http://groups.yahoo.com/group/sql-general
Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/sql-general/

<*> To unsubscribe from this group, send an email to:
sql-general-unsubscribe (AT) yahoogroups (DOT) com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/

I answered a similar problem a while ago under the heading "dynamic
transpose". The problems are essentially the same in that you have a
variable number of AddressPhone rows to 1 one address row. With a bit
of fiddling of the above answer you should be able to amend my code to
do answer your problem.

Hope this helps


Duncan