|
|
Posted by satish on 03/14/06 09:16
Values of two columns in two different tables--presentation using
select
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
create table address(col1 varchar(20),col2 int, col3 int)
insert into address values ('x',12,13)
insert into address values ('y',15,19)
insert into address values ('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
create table addressphone(col4 int, col5 varchar(50),col6 int)
insert into addressphone values(13,'213-455-9876',1)
insert into addressphone values(13,'415-564-6546',2)
insert into addressphone values(13,'543-987-5677',3)
insert into addressphone values(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
Navigation:
[Reply to this message]
|