Returning a column with row information 1

[BiggerBrother]

Ok, I know that isn't particularly good practice, but I hope there is an easier solution.

I have two tables, customer and customer_phone. The customer table has an id col, cust_id, which links the two tables. The phone table has a number col, a type col, and a few more.

What I need is to be able to return a recordset which comprises of the following:
c.firstname
c.lastname
c.title
cp.number (where cp.type=1)
cp.number (where cp.type=2)
cp.number (where cp.type=3)

I currently have this as a left join query, with a left join for each instance of the phone number eg:

SELECT c.first, c.last, c.title, c1.number, c2.number, c3.number from customer c left join customer_phone c1 on c.cust_id = c1.cust_id left join cust...........
where c1.type ='1' and c2.type = '2' and c3.type= '3'

Is there an easier way which doesn't involve so many left joins? My concern is that I'm looking at returning 100 rows per page for a report, and currently I need to return 6 number types, and other relevant information for each phone number.

For other reasons, this all needs to be handled by one SQL query, and returned into the one recordset.

Thanks for looking,

BB

 

[r937]

select c.firstname
     , c.lastname
     , c.title
     , max(case when cp.type=1
                then cp.number end) as type1_number
     , max(case when cp.type=2
                then cp.number end) as type2_number
     , max(case when cp.type=3
                then cp.number end) as type3_number
  from customer as c
left outer
  join customer_phone as cp 
    on cp_phone.cust_id = c.cust_id
group
    by c.firstname
     , c.lastname
     , c.title

r937.com | rudy.ca

 

[BiggerBrother]

How do you do it? Everytime I get stuck with a little SQL query issue, there you are with the answer. Everytime! Thanks again, and have another star.

Cheers

BB

 

[r937]

how do i do it? practice, practice, practice

thanks for the kind words



r937.com | rudy.ca