CODEWITHSUNDEEP
mysqlsql
KodeFor.Me
KodeFor.Me

Reputation: 13511

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.

Upvotes: 2

Views: 76

Answers (4)

Alex
Alex

Reputation: 17289

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'

Upvotes: 2

P. Kouvarakis
P. Kouvarakis

Reputation: 1943

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')

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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

Upvotes: 4

I wrestled a bear once.
I wrestled a bear once.

Reputation: 23379

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

Upvotes: 1

Related Questions