PHP MySQL join one table with another one but twice

Question

I'm trying to join two tables but the last one twice. What I've got is this:

Table 1
    id name email
    476 Lars Lyngsoe   [email protected]
    478 Lars2 Lyngsoe2 [email protected]
    495 Lars3 Lyngso3  [email protected]

Table 2
    user_id  profile_key      profile_value
    476     'profile.klasse'  '10A'
    495     'profile.klasse'  '10B'
    476     'profile.phone'   '12345678'
    478     'profile.klasse'  '10A'
    478     'profile.phone'   '23432123'
    495     'profile.phone'   '21212143'

Where id in Table 1 equals user_id in Table 2

I've tried to join and make subqueries but nothing works. What I want to achieve is this:

Table
    id  name           email              class  phone
    476 Lars Lyngsoe   [email protected]     '10A'  '12345678'
    478 Lars2 Lyngsoe2 [email protected]  '10A'  '23432123' 
    495 Lars3 Lyngso3  [email protected]  '10B'  '21212143'

Thank's for your help.

Lars

instead of joining twice invert the join so table 2 is your primary table then inner join for each user id against tbl 1 so each record returns multiple rows then just where clause tbl1.user_id vs the passed in id and it'll return what you want. — Dave
– Dave, Commented Sep 1, 2015 at 12:11

Beowolve · Accepted Answer · 2015-09-01 11:27:42Z

1

This should work:

SELECT t1.id as id, t1.name, t1.email, t2a.profile_value as class, t2b.profile_value as phone
FROM Table1 as t1
LEFT JOIN Table2 t2a ON t2a.user_id = t1.id AND t2a.profile_key = 'profile.klasse'
LEFT JOIN Table2 t2b ON t2b.user_id = t1.id AND t2b.profile_key = 'profile.phone'

edited Sep 1, 2015 at 11:27

answered Sep 1, 2015 at 11:21

Beowolve

5782 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Eloims Over a year ago

AFAICR on MySQL, table aliases might need the AS keyword. LEFT JOIN Table2 AS t2a but I'm not sure anymore

Beowolve Over a year ago

You are right I should have used a second look, fixed it

Strawberry Over a year ago

@Eloims your recollection is flawed

Lars Lyngsøe Højberg Over a year ago

Thank's a lot for Your help! What if I only want to show rows where profile_value equals eg '10A' - thanks!

lafor · Accepted Answer · 2015-09-01 11:26:29Z

0

What you need is two joins with specific profile_key values:

SELECT t1.id, t1.name, t1.email, t2.profile_value AS class, t3.provile_value AS phone
FROM Table1 t1
LEFT JOIN Table2 t2 ON (t1.id = t2.user_id AND t2.profile_key='profile.klasse')
LEFT JOIN Table2 t3 ON (t1.id = t3.user_id AND t3.profile_key='profile.phone')

answered Sep 1, 2015 at 11:26

lafor

12.8k4 gold badges34 silver badges36 bronze badges

Comments

Satender K · Accepted Answer · 2015-09-01 12:12:11Z

0

I have written a database query for you. I hope it will resolve your problem :

Query

SELECT 
    t1.id,
    t1.`name`,
    t1.email,
    CASE t2.profile_key
        WHEN 'profile.klasse' THEN t2.profile_value
    END AS 'class',
    (SELECT profile_value FROM table2 WHERE profile_key = 'profile.phone' AND user_id = t1.id) AS 'phone'
FROM
    table1 t1
        LEFT JOIN
    table2 t2 ON t1.id = t2.user_id AND t2.profile_key = 'profile.klasse' ORDER BY id;

Click SQL Fiddle

edited Sep 1, 2015 at 12:12

answered Sep 1, 2015 at 12:05

Satender K

5813 silver badges14 bronze badges

2 Comments

Lars Lyngsøe Højberg Over a year ago

Thank You very much - I will play around with Your suggestion.

Lars Lyngsøe Højberg Over a year ago

What if I want to show rows where profile_value equals '10A'? - Thanks

Strawberry · Accepted Answer · 2015-09-01 12:18:40Z

0

Or, slower but simpler...

SELECT t1.*
     , MAX(CASE WHEN t2.profile_key = 'profile.klasse' THEN t2.profile_value END) klasse
     , MAX(CASE WHEN t2.profile_key = 'profile.phone' THEN t2.profile_value END) phone
  FROM t1
  JOIN t2 
    ON t2.user_id = t1.user_id
 GROUP
    BY t1.user_id

answered Sep 1, 2015 at 12:18

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Collectives™ on Stack Overflow

PHP MySQL join one table with another one but twice

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related