Clever Geek Handbook

MySQL query for three tables

I have three tables with people, attributes and potential value for attributes. I can't seem to find a query to display all people, each person's attributes and their missing / null attributes.

Here's an example table ...

attributes
+---------------------+
| attribute_name (col)|
+---------------------+
| name                |
+---------------------+
| age                 |
+---------------------+
| gender              |
+---------------------+
| email               |
+---------------------+

people
+-----------+----------+
| person_id | value_id |
+-----------+----------+
| 2         | 7        |
+-----------+----------+
| 2         | 9        |
+-----------+----------+
| 3         | 8        |
+-----------+----------+

values
+---------------+----------------+-------+
| value_id (pk) | attribute_name | value |
+---------------+----------------+-------+
| 7             | age            | 35    |
+---------------+----------------+-------+
| 8             | age            | 28    |
+---------------+----------------+-------+
| 9             | gender         | male  |
+---------------+----------------+-------+


How do I join three tables to display something like this?

+-----------+----------+-----------------+--------+
| person_id | value_id | attribute_name  | value  |
+-----------+----------+-----------------+--------+
| 2         | 7        | age             | 35     |
+-----------+----------+-----------------+--------+
| 2         | 9        | gender          | male   |
+-----------+----------+-----------------+--------+
| 2         | NULL     | name            | NULL   |
+-----------+----------+-----------------+--------+
| 2         | NULL     | email           | NULL   |
+-----------+----------+-----------------+--------+
| 3         | 8        | age             | 28     |
+-----------+----------+-----------------+--------+
| 3         | NULL     | gender          | NULL   |
+-----------+----------+-----------------+--------+
| 3         | NULL     | name            | NULL   |
+-----------+----------+-----------------+--------+
| 3         | NULL     | email           | NULL   |
+-----------+----------+-----------------+--------+
+3


source to share


4 answers 


SELECT  a.person_ID, 
        MAX(c.value_ID) value_ID, 
        b.attribute_name ,  
        MAX(c.value) Value
FROM    people a
        CROSS JOIN attributes b       
        LEFT JOIN `values` c
            ON  a.value_ID = c.value_ID AND
                b.attribute_name = c.attribute_name
GROUP BY a.person_ID, b.attribute_name


+3


source


I would do a join of three tables with a WHERE clause (SQL basics).

Did you do a search before posting?



For null, this is the post MySQL Show Null Results in Query - with INNER JOIN or this Mysql joins four tables and show NULL

; -)

0


source 


select
    person_id,
    attributes.attribute_name as attribute_name,
    values.value as value
from people
cross join attributes
left join values on people.value_id=values.value_id
                and attributes.attribute_name=values.attribute_name
0


source


Using your expected result as a starting point, you essentially want the cross product of attributes and individuals from to people

remain connected to the result of the (n inner) join people

- values

. And this is how you could script that in SQL (especially in MySQL):

SELECT
  p.person_id,
  v.value_id,
  a.attribute_name,
  v.value
FROM 
  (SELECT DISTINCT person_id FROM people) p
  CROSS JOIN attributes a
  LEFT JOIN
    people d
    INNER JOIN `values` v ON d.value_id = v.value_id
  ON p.person_id = d.person_id
  AND a.attribute_name = v.attribute_name
;
0


source






More articles:


All Articles