Geek Answers Handbook

MySQL double LEFT OUTER JOIN

AT

I have two profile tables and a name , the profile table contains some metadata related to the user. The name table contains all the possible names that the profile might have.

I am trying to create a MYSQL query that will give me profile.age, given name and last name for all profiles, even if they don't have a first or last name

Tables

Profile

    + ------- + --------- +
    | ID | AGE |
    + ------- + --------- +
    | 0 | 10 |
    | 1 | 20 |
    | 2 | 30 |
    | 3 | 40 |
    + ------- + --------- +

Name

    + ------------ + -------- + -------- +
    | PROFILE_ID | TYPE | NAME |
    + ------------ + -------- + -------- +
    | 0 | 0 | Jo |
    |      0     |   1    | Blog   |
    |      1     |   0    | Jim    |
    |      2     |   1    | Smith  |
    +------------+--------+--------+

    Type 0 = Given Name
    Type 1 = Family Name

Quert

, .

SELECT given.name AS 'given_name', family.name AS 'family_name', profile.age
FROM profile
LEFT OUTER JOIN name given ON profile.id = given.profile_id
LEFT OUTER JOIN name family ON profile.id = family.profile_id
WHERE given.type = 0 
AND profile_id.type = 1 
LIMIT 0 , 30

,

    +------------+-------------+--------+
    | GIVEN_NAME | FAMILY_NAME | ADE    |
    +------------+-------------+--------+
    | Jo         | Blog        | 10     |
    | Jim        | NULL        | 20     |
    | NULL       | Smith       | 30     |
    | NULL       | NULL        | 40     |
    +------------+-------------+--------+

,

    +------------+-------------+--------+
    | GIVEN_NAME | FAMILY_NAME | AGE    |
    +------------+-------------+--------+
    | Jo         | Blog        | 10     |
    +------------+-------------+--------+

, , LEFT OUTER JOIN NULL. ? , joun NULL?

+5
3

WHERE , LEFT JOIN ed, NULL (.. , ). given.type = 0 ON WHERE. , WHERE.

SELECT given.name AS 'given_name', family.name AS 'family_name', profile.age
FROM profile
LEFT OUTER JOIN name given ON profile.id = given.profile_id AND given.type = 0 
LEFT OUTER JOIN name family ON profile.id = family.profile_id AND family.type = 1
LIMIT 0 , 30
+4

type 0 1 ( where), null s.

SELECT given.name AS 'given_name', family.name AS 'family_name', profile.age
    FROM profile
    LEFT OUTER JOIN name given 
       ON profile.id = given.profile_id and given.type = 0 
    LEFT OUTER JOIN name family 
       ON profile.id = family.profile_id and profile.type = 1 
    LIMIT 0 , 30
+5
SELECT N1.id, N1.name,N2.name, A3.age 
FROM name N1, name N2, age A3 
WHERE N1.id=N2.id AND N1.id=A3.id 
  AND N1.type=0   AND N2.type=1

name age

-2

More articles:


All Articles