CODEWITHSUNDEEP
phpmysqljoinsubquery
Lars Lyngsøe Højberg
Lars Lyngsøe Højberg

Reputation: 75

PHP MySQL join one table with another one but twice

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

Upvotes: 1

Views: 71

Answers (4)

Strawberry
Strawberry

Reputation: 33945

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

Upvotes: 0

Satender K
Satender K

Reputation: 581

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

Upvotes: 0

Beowolve
Beowolve

Reputation: 558

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'

Upvotes: 1

lafor
lafor

Reputation: 12776

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')

Upvotes: 0

Related Questions