Join table with where clause

I have two tables in MySQL; person table and attribute table. The attribute table works as a key value store for person.

PERSON
| id |    name |
|----|---------|
|  1 |   Alice |
|  2 |     Bob |
|  3 | Charlie |
|  4 |    Dave |

ATTRIBUTE
| id | person_id |           key |                                        value |
|----|-----------|---------------|----------------------------------------------|
|  1 |         1 |           age |                                           20 |
|  2 |         1 |      hometown |                                      Seattle |
|  3 |         2 |           age |                                           20 |
|  4 |         3 |         hobby |                                     baseball |
|  5 |         3 | ageOfDaughter |                                           20 |
|  6 |         4 |         email |                             dave@example.com |
|  7 |         4 |   latestTweet | The most beautiful thing in the world is me. |

In the example above, now I want to get everyone whose age is 20 with all of their attributes. Is it possible to get the result with one query?

(See SQL Fiddle here: http://sqlfiddle.com/#!9/9e9e5b/1)

4 answers

  • answered 2018-05-16 06:06 N00b Pr0grammer

    You just have to create an inner join and then provide a where clause with your condition.

    SELECT p.name as Name, a.value as Age 
    FROM attribute a INNER JOIN person p 
    ON a.person_id = p.id
    WHERE a.key = 'age'
    

    This will provide you with the Person and Age values, then further you can add additional clause to get your intended results. Something like this:

    SELECT p.name as Name, a.value as Age 
    FROM attribute a INNER JOIN person p 
    ON a.person_id = p.id
    WHERE a.person_id IN (SELECT person_id FROM attribute WHERE `key` LIKE 'age')
        AND a.value = 20
    

    Hope this helps!

  • answered 2018-05-16 06:16 uvishere

    I think You need to create a nested query to do that following with inner joins. Here's the example:

    select sub.*
     from (
      SELECT name, person_id, attribute.key, attribute.value FROM person
        inner join attribute on person.id=attribute.person_id
     ) sub
    where sub.key="age" && sub.value=20;
    

    The example fiddle can be found here: sqlfiddle

  • answered 2018-05-16 06:48 s-onuma

    Thanks to the comments, I finally found the answer. Thank you everyone.

    SELECT p.*, GROUP_CONCAT(a.key) as 'keys', GROUP_CONCAT(a.value) as 'values'
    FROM person p
    LEFT OUTER JOIN attribute a ON p.id = a.person_id
    WHERE p.id IN (
      SELECT a.person_id FROM attribute a WHERE a.key = 'age' AND a.value = '20'
    )
    GROUP BY p.id
    

    http://sqlfiddle.com/#!9/9e9e5b/52

  • answered 2018-05-16 06:54 P.Salmon

    First sub query to establish if age = 20 is present then join attribute to get all the attributes

        SELECT * 
        FROM
        (
        SELECT P.ID PID, P.NAME PNAME, A.ID AID, A.PERSON_ID
        FROM PERSON P
        JOIN ATTRIBUTE A ON A.PERSON_ID = P.ID 
        WHERE `KEY` = 'AGE' AND VALUE = (20)
        ) S
        JOIN
        ATTRIBUTE A ON A.PERSON_ID = S.PID;
    
    +-----+-------+-----+-----------+----+-----------+----------+---------+
    | PID | PNAME | AID | PERSON_ID | id | person_id | key      | value   |
    +-----+-------+-----+-----------+----+-----------+----------+---------+
    |   1 | Alice |   1 |         1 |  1 |         1 | age      | 20      |
    |   1 | Alice |   1 |         1 |  2 |         1 | hometown | Seattle |
    |   2 | Bob   |   3 |         2 |  3 |         2 | age      | 20      |
    +-----+-------+-----+-----------+----+-----------+----------+---------+
    3 rows in set (0.00 sec)