Values of two columns in two different tables--presentation using select

[satish]

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

[David Portas]

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/m...S,SQL.90).aspx
--

[satish]

thanking you very much

i works a lot
satish

[Madhivanan]

Also refer
http://sqljunkies.com/WebLog/amachan...x?Pending=true

Madhivanan

[Erland Sommarskog]

satish (sa**********************@gmail.com) writes:

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

Here is a query that only joins addressphone once:
select a.col1, a.col2, a.col3,
MIN(CASE ap.col6 WHEN 1 THEN ap.col5 END),
MIN(CASE ap.col6 WHEN 2 THEN ap.col5 END),
MIN(CASE ap.col6 WHEN 3 THEN ap.col5 END)
FROM address a
LEFT JOIN addressphone ap ON a.col3 = ap.col4
GROUP BY a.col1, a.col2, a.col3
go

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx