Posted by Erland Sommarskog on 09/26/79 11:42

satish (satishkumar.gourabathina@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: ORDER BY in VIEW not working
• Prev in forum: Re: Error (8626) while inserting record into table with text field and which is the base for indexed view
• Thread view: Re: Values of two columns in two different tables--presentation using select

[Reply to this message]