CODEWITHSUNDEEP
phpmysqlinner-join
ipixel
ipixel

Reputation: 529

MySQL inner join AND turn rows into colums

I'm having difficulty figuring out how to create the proper syntax for my query.

Here is what i'm pulling. I have 2 tables.

Table 1 : Fields  (user_id, name)
Table 2 : Fields  (user_id, type, are_code, phone_number).

Table 1 can only have 1 record per user_id. 

1 | John Doe

Table 2 can have up to 3 records per user_id: 

1 | Home | 123 | 456.4567
1 | Work | 000 | 987.1467
1 | Mobi | 098 | 987.1756

How can i select everything so that my table will result in 1 record pulled like so :

user_id | name | home# | work# | mobi#

I tried this, which duplicates and doubles rows based on amount of entries within Table 2.

SELECT a.user_id, 
       b.area_code, b.phone_number
FROM users a
INNER JOIN user_contact_phones b ON a.user_id = b.user_id

That unfortunately returned 3 rows which is not good :(.

1 | John Doe | area | home# | 
1 | John Doe | area | work# |
1 | John Doe | area | mobi# |

Any help and or pointers would be greatly appreciated.

Upvotes: 1

Views: 75

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this out:

SELECT
  u.user_id,
  u.name,
  MAX(CASE WHEN p.type = 'Home' THEN phone_number END) HomeNumber,
  MAX(CASE WHEN p.type = 'Work' THEN phone_number END) WorkNumber,
  MAX(CASE WHEN p.type = 'Mobi' THEN phone_number END) MobiNumber
FROM phones p
JOIN users u ON p.user_id = u.user_id
GROUP BY u.user_id, u.name

Output:

| USER_ID |     NAME | HOMENUMBER | WORKNUMBER | MOBINUMBER |
|---------|----------|------------|------------|------------|
|       1 | John Doe |   456.4567 |   987.1467 |   987.1756 |

Fiddle here.

Also note that you can remove u.name if u.user_id determines u.name... which is most likely the case as it seems to be a primary key. That would speed things up a little bit.

Note: This assumes that you cant have more than one same type for the same user (as it is in your example data, which only has one column for home, work and mobile.

Upvotes: 2

David Fleeman
David Fleeman

Reputation: 2638

Here's a solution that will work:

select u.user_id, u.name,
    thome.area_code as home_area_code, thome.phone_number as home_phone_number,
    twork.area_code as work_area_code, twork.phone_number as work_phone_number,
    tmobi.area_code as mobi_area_code, tmobi.phone_number as mobi_phone_number  
from table1 u
    left outer join table2 thome on u.user_id = thome.user_id and thome.type = 'Home'
    left outer join table2 twork on u.user_id = twork.user_id and twork.type = 'Work'
    left outer join table2 tmobi on u.user_id = tmobi.user_id and tmobi.type = 'Mobi'

Please note the use of left outer join instead of inner join in case the record for a particular type does not exist. You will get null values for those columns in your result set with left outer join. With inner join, you would not get a result for a user that did not have all three types. Good luck!

Upvotes: 0

ritesh
ritesh

Reputation: 2265

Use user_contact_phones.type to get exact what you want, like-

SELECT a.user_id, 
       b.area_code, b.phone_number
FROM users a
INNER JOIN user_contact_phones b ON a.user_id = b.user_id where b.type='Home'

Upvotes: 1

Related Questions