Merianos Nikos Merianos Nikos - 6 months ago 11
SQL Question

How to join those two tables in MySQL

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.

Answer

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'