fetch multiple mysql table like pivot table with key and values

Question

I have 3 table for my ads portal, I would 3 table join a single query.

Please look this schema http://sqlfiddle.com/#!2/8b74b/3

CREATE TABLE `ads` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ads_title` CHAR(80) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)

);


CREATE TABLE `ads_keys` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `key` CHAR(25) NULL DEFAULT NULL ,
    `inlist` INT(1) UNSIGNED ZEROFILL NULL DEFAULT NULL ,
    PRIMARY KEY (`id`)
);
CREATE TABLE `ads_values` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ads_id` INT(3) NULL DEFAULT NULL,
    `key_id` INT(10) NULL DEFAULT NULL,
    `value` INT(10) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ads_id` (`ads_id`, `key_id`)
);
INSERT INTO `ads` VALUES (1, 'AAA');
INSERT INTO `ads` VALUES (2, 'BBB');
INSERT INTO `ads` VALUES (3, 'CCC');
INSERT INTO `ads` VALUES (4, 'DDD');
INSERT INTO `ads` VALUES (5, 'EEE');
INSERT INTO `ads` VALUES (6, 'FFF');
INSERT INTO `ads_keys` VALUES (1, 'KM', 1);
INSERT INTO `ads_keys` VALUES (2, 'OIL', 0);
INSERT INTO `ads_keys` VALUES (3, 'YEAR', 1);
INSERT INTO `ads_keys` VALUES (4, 'COLOR', 0);
INSERT INTO `ads_keys` VALUES (5, 'SPEED', 0);

INSERT INTO `ads_values` VALUES (1, 1, 1, 89000);
INSERT INTO `ads_values` VALUES (2, 1, 2, 200);
INSERT INTO `ads_values` VALUES (3, 1, 3, 2010);
INSERT INTO `ads_values` VALUES (4, 1, 4, 1);
INSERT INTO `ads_values` VALUES (5, 1, 5, 180);
INSERT INTO `ads_values` VALUES (6, 2, 1, 13000);
INSERT INTO `ads_values` VALUES (7, 2, 2, 150);
INSERT INTO `ads_values` VALUES (8, 2, 3, 2008);
INSERT INTO `ads_values` VALUES (9, 2, 4, 1);
INSERT INTO `ads_values` VALUES (10, 2, 5, 160);
INSERT INTO `ads_values` VALUES (11, 3, 1, 79800);
INSERT INTO `ads_values` VALUES (12, 3, 2, 172);
INSERT INTO `ads_values` VALUES (13, 3, 3, 2008);
INSERT INTO `ads_values` VALUES (14, 3, 4, 2);
INSERT INTO `ads_values` VALUES (15, 3, 5, 178);
INSERT INTO `ads_values` VALUES (16, 4, 1, 56781);
INSERT INTO `ads_values` VALUES (17, 4, 2, 127);
INSERT INTO `ads_values` VALUES (18, 4, 3, 2009);
INSERT INTO `ads_values` VALUES (19, 4, 4, 3);
INSERT INTO `ads_values` VALUES (20, 4, 5, 156);
INSERT INTO `ads_values` VALUES (21, 5, 1, 10200);
INSERT INTO `ads_values` VALUES (22, 5, 2, 205);
INSERT INTO `ads_values` VALUES (23, 5, 3, 2000);
INSERT INTO `ads_values` VALUES (24, 5, 4, 3);
INSERT INTO `ads_values` VALUES (25, 5, 5, 160);
INSERT INTO `ads_values` VALUES (26, 6, 1, 45877);
INSERT INTO `ads_values` VALUES (27, 6, 2, 150);
INSERT INTO `ads_values` VALUES (28, 6, 3, 2009);
INSERT INTO `ads_values` VALUES (29, 6, 4, 1);
INSERT INTO `ads_values` VALUES (30, 6, 5, 168);

I want to below query result

ads_id - ads_title - INLIST KEYS /*if into ads_keys table field's value 1 then this rows to columns*/

For example;

+----+-----------+-----------+------------+
| id | ads_title | KM        | YEAR       |  -> If inlist=1 to columns to title 
+----+-----------+-----------+------------+         (looks KM and YEAR inlist=1)
|  1 | AAA       | val       | val        |
|  2 | BBB       | val       | val        |
|  3 | CCC       | val       | val        |
|  4 | DDD       | val       | val        |
|  5 | EEE       | val       | val        |
|  6 | FFF       | val       | val        |
+----+-----------+-----------+------------+

How I put this row to columns? And How I filtered this query. For example color=1 or speed=160

EDIT: rows to columns is works fine. Thanks @bluefeet. I have new requirements.

See http://sqlfiddle.com/#!2/8b74b/12 . How I filter filter by shown key?

I think this kind of thing is best left to professionals!! – Strawberry Aug 09 '13 at 09:35 — Strawberry, Aug 09 '13 at 09:35

score 7 · Answer 1 · answered Aug 09 '13 at 11:51

7

In MySQL to convert rows into columns, you will have to apply an aggregate function along with a CASE expression.

If you have a limited number of known values, then you could hard-code the query:

select a.id,
  a.ads_title,
  max(case when k.`key` ='KM' then v.value end) `KM`,
  max(case when k.`key` ='Year' then v.value end) `Year`
from ads a
left join ads_values v 
  on a.id = v.ads_id
left join ads_keys k
  on v.key_id = k.id
where k.inlist = 1
group by a.id;

See SQL Fiddle with Demo.

Now if you want to alter the results based on the inlist value without having to rewrite the query, then you can use a prepared statement with dynamic SQL:

SET @sql = NULL;
set @keyNumber = 1;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when k.`key` = ''',
      `key`,
      ''' then v.value end) as `', 
      `key`, '`')
  ) INTO @sql
FROM ads_keys
where inlist = @keyNumber;

SET @sql = CONCAT('SELECT a.id,
                    a.ads_title, ', @sql, ' 
                  from ads a
                  left join ads_values v 
                    on a.id = v.ads_id
                  left join ads_keys k
                    on v.key_id = k.id
                  where k.inlist = ', @keyNumber, '
                  group by a.id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo. Both versions will give a result:

| ID | ADS_TITLE |    KM | YEAR |
---------------------------------
|  1 |       AAA | 89000 | 2010 |
|  2 |       BBB | 13000 | 2008 |
|  3 |       CCC | 79800 | 2008 |
|  4 |       DDD | 56781 | 2009 |
|  5 |       EEE | 10200 | 2000 |
|  6 |       FFF | 45877 | 2009 |

answered Aug 09 '13 at 11:51

Taryn

242,637
56
362
405

Thank you so much. Can I add another key and value in where clause? Like key_id=x – maverabil Aug 09 '13 at 19:59
@maverabilisim Yes you can add an `or key_id = yourvalue` to the query – Taryn Aug 09 '13 at 20:02
When I add AND v.key_id=4 AND v.value=4 not results. http://sqlfiddle.com/#!2/8b74b/18 – maverabil Aug 09 '13 at 20:19
1

@maverabilisim Your new question is not clear, see this demo -- http://sqlfiddle.com/#!2/8b74b/22 -- is that what you are asking? If not, then you need to provide more details – Taryn Aug 09 '13 at 20:21
Thank you again for your support. I'm grateful. row to column works fine. But When I using AnotherKeyID=value different non-inlist value not get any result. Sorry for my english – maverabil Aug 09 '13 at 20:26
1

@maverabilisim You are not getting any rows because you don't have any matching `key_id=4 and value=4` with an inlist=1 – Taryn Aug 09 '13 at 20:38
Yeah. it's correct. But I must filter by another key. Have you any idea or case? – maverabil Aug 09 '13 at 20:41
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/35165/discussion-between-maverabilisim-and-bluefeet) – maverabil Aug 09 '13 at 20:44
4

@maverabilisim You already have an answer for your original question, you can't just edit it to add new requirements. Either ask another question (if it really exists the need) or work over the answer you already have – Lamak Aug 09 '13 at 20:48
1

100%. No [chameleon questions](http://meta.stackexchange.com/questions/43478/exit-strategies-for-chameleon-questions) please. – Aaron Bertrand Aug 09 '13 at 20:53

score 1 · Answer 2 · edited May 23 '17 at 12:28

Since this is tagged also as PHP I'll first give you a hint with php. Alternatively you could make a mysql procedure or a function for the results. More easier way is, if your ads_keys-table is static (no more keys added there) you can try to do it without functions like here

<?
   $d = $db -> getRows('select ads.*, ads_keys.key, ads_values.value from ads, ads_keys, ads_values where ads_keys.inlist = 1 and ads_values.key_id = ads_keys.id and ads_values.ads_id = ads.id');
   $r = $keys = array();
   foreach ($d as $k => $v) {
       if (!isset($r[$v['id']])) {
            $r[$v['id']]= array ('id' => $v['id'], 'ads_title' => $v['ads_title'], $v['key'] => $v['value']);
       } else if (!isset($r[$v['id']][$v['key']])) {
           $r[$v['id']][$v['key']] = $v['value'];
       }

       $keys[] = $v['key'];
   }
   $keys = array_unique($keys);

    echo "id|ads_title|";
    foreach ($keys as $key) {
        echo $key."|";
    }
    echo "\n";
    foreach ($r as $res) {
        echo $res['id']."|";
        echo $res['ads_title']."|";
        foreach ($keys as $key) {
            echo (isset($res[$key]) ? $res[$key] : ' ')."|";
        }

        echo "\n";
    }
?>

The result:

id|ads_title|KM|YEAR|
1|AAA|89000|2010|
2|BBB|13000|2008|
3|CCC|79800|2008|
4|DDD|56781|2009|
5|EEE|10200|2000|
6|FFF|45877|2009|

fetch multiple mysql table like pivot table with key and values

2 Answers2