MYSQL - Join 2 tables with full rows on left table

1
votes

Could you please help me to resolve this issue?

I have 2 tables as below:

attributes

| id | name         |
|----+--------------|
| 1  | first_name   |
| 2  | last_name    |
| 3  | email        |

attribute_values

| uid   | attr_id   | value     |
|-------+-----------|-----------|
| 1     | 1         | Hello     |
| 1     | 2         | world     |
|-------+-----------|-----------|
| 2     | 1         | A         |   
| 2     | 2         | B         |
| 2     | 3         | [email protected] |

I want to write a query to select attributes of a user even if attribute is not exist in table attribute_values. Then, I would like to get result with null value.

For example for selecting user id 1

| name          | value     |
|---------------|-----------|
| first_name    | Hello     |
| last_name     | world     |
| email         | NULL      |

user id 2

| name          | value     |
|---------------|-----------|
| first_name    | A         |
| last_name     | B         |
| email         | [email protected] |

user id 3 (not exist in attribute_values)

| name          | value     |
|---------------|-----------|
| first_name    | NULL      |
| last_name     | NULL      |
| email         | NULL      |

Is there best way with one query to get result as 3 above examples?

Thanks.

1
mysqlsqlselectjoin

1 Answers

2
votes
SELECT  a.*, b.value
FROM    attributes a
        LEFT JOIN attribute_values b
            ON a.ID = b.attr_id AND b.uid = 1