How to join those two tables in MySQL

Question

I have two tables with the following structure:

|=================|
| posts           |
|=================|
| ID | Title      |
|-----------------|
| 1  | Title #1   |
|-----------------|
| 2  | Title #1   |
|-----------------|
| 3  | Title #1   |
|-----------------|
| 4  | Title #1   |
|-----------------|
| 5  | Title #1   |
|-----------------|

and

|==========================================|
| meta                                     |
|==========================================|
| id | post_id | meta_key   | meta_value   |
|------------------------------------------|
| 1  | 1       | key_one    | value for #1 |
|------------------------------------------|
| 2  | 1       | key_two    | value for #1 |
|------------------------------------------|
| 3  | 1       | key_three  | value for #1 |
|------------------------------------------|
| 4  | 2       | key_one    | value for #2 |
|------------------------------------------|
| 5  | 2       | key_three  | value for #2 |
|------------------------------------------|
| 6  | 3       | key_one    | value for #3 |
|------------------------------------------|
| 7  | 3       | key_three  | value for #3 |
|------------------------------------------|

and I need to get the following single result:

|----------------------------------------------------------------|
| ID | Post Title | Meta Key One | Meta Key Two | Meta Key Three |
|----------------------------------------------------------------|
| 1  | Title #1   | value for #1 | value for #1 | value for #1   |
|----------------------------------------------------------------|
| 2  | Title #2   | value for #2 | null         | value for #2   |
|----------------------------------------------------------------|
| 3  | Title #3   | value for #3 | null         | value for #3   |
|----------------------------------------------------------------|

But I don't know how to do this.

The SQL Query I have build until now is this:

SELECT
   `p`.`ID` AS `ID`,
   `p`.`Title` AS `Post Title`,
   `mt1`.`meta_value` AS `Meta Key One`,
   `mt2`.`meta_value` AS `Meta Key One`,
FROM
    posts AS `p`
    LEFT JOIN `meta` AS `mt1` ON ( `p`.`ID` = `mt1`.`post_id` )
    LEFT JOIN `meta` AS `mt2` ON ( `p`.`ID` = `mt2`.`post_id` )
WHERE
    1 = 1
    AND `mt1`.`meta_key` = 'key_one'
    AND `mt2`.`meta_key` = 'key_three';

The problem is that if I add a third LEFT JOIN in meta table to use it later on in WHERE clause and say mt1.meta_key = 'key_two' I get only one record instead of three.

Does anyone know how can I achieve this with a single query ?

I don't know if that helps, but I have create an SQL Fiddle here : http://sqlfiddle.com/#!9/af591f/1

Note that the column names in fiddle doesn't meet the ones in my example, but the problem remains the same.

As a general rule: criteria on an outer joined table belongs in the `ON` clause not in the `WHERE` clause. This is where your query fails. — Thorsten Kettner, May 18 '16 at 13:55

score 4 · Answer 1 · answered May 18 '16 at 13:50

You can use conditional aggregation for this:

SELECT p.ID, p.Title AS 'Post Title',
       MAX(CASE WHEN meta_key = 'key_one' THEN meta_value END) AS 'Meta Key One',
       MAX(CASE WHEN meta_key = 'key_two' THEN meta_value END) AS 'Meta Key Two',
       MAX(CASE WHEN meta_key = 'key_three' THEN meta_value END) AS 'Meta Key Three'
FROM posts AS p
LEFT JOIN meta AS m ON p.ID = m.post_id
GROUP BY p.ID, p.Title

The benefit of this method is that you use LEFT JOIN just once, so it is easily extensible in order to accommodate additional key values.

Demo here

score 2 · Accepted Answer · answered May 18 '16 at 13:50

2

http://sqlfiddle.com/#!9/af591f/4

SELECT 
  `b`.`id` AS `ID`,
  `b`.`title` AS `Title`,
  mt1.meta_value `KeyOne`,
  mt2.meta_value `KeyTwo`,
  mt3.meta_value `KeyThree`
FROM 
  `base` as `b`
  LEFT JOIN `meta` mt1 ON b.id = mt1.base_id AND mt1.meta_key = 'key_one' 
  LEFT JOIN `meta` mt2 ON b.id = mt2.base_id AND mt2.meta_key = 'key_two' 
  LEFT JOIN `meta` mt3 ON b.id = mt3.base_id AND mt3.meta_key = 'key_three'

answered May 18 '16 at 13:50

Alex

16,739
1
28
51

You should use OUTER join for this, because otherwise you wont get the second and third desired output row. – Andreas Scheibleger May 18 '16 at 13:52
1

you can check : http://stackoverflow.com/a/406333/4421474 there is no difference between `LEFT JOIN` and `LEFT OUTER JOIN` – Alex May 18 '16 at 13:55

score 1 · Answer 3 · answered May 18 '16 at 13:49

You join tables by column, you don't join columns by columns.. Since you only have two tables you only need one join. Start with this query and change the * to whatever columns you need.

select 
    * --or whatever
from
posts p
left join meta m on p.id = m.post_id

score 1 · Answer 4 · answered May 18 '16 at 13:50

Try this:

SELECT
   `p`.`ID` AS `ID`,
   `p`.`Title` AS `Post Title`,
   `mt1`.`meta_value` AS `Meta Key One`,
   `mt2`.`meta_value` AS `Meta Key Two`,
   `mt3`.`meta_value` AS `Meta Key Three`
FROM
    posts AS `p`
    LEFT JOIN `meta` AS `mt1` 
        ON ( `p`.`ID` = `mt1`.`post_id` AND `mt1`.`meta_key` = 'key_one')
    LEFT JOIN `meta` AS `mt2` 
        ON ( `p`.`ID` = `mt2`.`post_id` AND `mt2`.`meta_key` = 'key_two')
    LEFT JOIN `meta` AS `mt3` 
        ON ( `p`.`ID` = `mt3`.`post_id` AND `mt3`.`meta_key` = 'key_three')

How to join those two tables in MySQL

4 Answers4