|
|
Posted by David Portas on 03/14/06 09:29
satish wrote:
> 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
Thanks for including the DDL. It does also help if you include keys
with your CREATE TABLE statements.
Try:
SELECT A.col1, A.col2, A.col3,
P1.col5 AS phone1, P2.col5 AS phone2, P3.col5 AS phone3
FROM address AS A
LEFT JOIN addressphone AS P1
ON A.col3 = P1.col4
AND P1.col6 = 1
LEFT JOIN addressphone AS P2
ON A.col3 = P2.col4
AND P2.col6 = 2
LEFT JOIN addressphone AS P3
ON A.col3 = P3.col4
AND P3.col6 = 3
/* LEFT JOIN addressphone AS P4 ... etc */;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|