How can I take the results of two database rows and condense them into a single row?

Question

I have a mysql db query that I'd like to take and condense into a single row. The output is currently two rows and will only ever be two rows. The reason I am getting two rows in the first place is because there will always be a primary and a secondary phone number associated with my result.

Since all of the data is exactly the same for both rows with the exception of the phone type (ptype) and second phone number, I'd like to just take those two columns and append them to the forst row.

I know this can be done but I can't remember how to do it.

Here is the code I am using:

SELECT
c.name,
c.address1,
p.ptype,
p.phone
FROM
customer AS c
Inner Join customeremp AS e ON c.customer_id = e.customer_id
Inner Join customerphone AS p ON e.customer_seq = p.customer_seq
WHERE
c.customer_id =  '1'

Answer 1

You need to create 2 joins on the phone table

 SELECTc.name,
 c.address1,
 p.ptype,
 p.phone,
 p2.ptype,
 p2.phone,
 FROMcustomer AS cInner 
 Join customeremp AS e ON   c.customer_id = e.customer_idInner 
 Join customerphone AS p ON e.customer_seq = p.customer_seq and p.pType = 1 (or whatever private means)
 Join customerphone AS p2 ON e.customer_seq = p2.customer_seq and p2.pType = 2 (or whatever the other type is)
 WHEREc.customer_id =  '1'

Edit: this query will only return customer which have BOTH phone types set. If they are optional, then you should consider changing the join into an outer join

Answer 2

Try this:

SELECT
   c.name,
   c.address1,
   IF(p.ptype = 'primary', p.phone, null)),
   IF(p.ptype = 'secondary', p.phone, null))
FROM customer JOIN (blah blah)
WHERE customer_id = 1
GROUP BY c.name, c.address1

If it doesn't work, you might need to put an aggregate function on the last two fields, eg: MAX(IF(p.ptype ...

Answer 3

select c.name,c.address1,primary.phone,secondary.phone
  from customer c
  join customeremp e
    on c.customer_id = e.customer_id
  join customerphone primary
    on primary.ptype = 'primary'
   and e.customer_seq = primary.customer_seq
  join customerphone secondary
    on secondary.ptype = 'secondary'
   and e.customer_seq = secondary.customer_seq
 where c.customer_id =  '1'