filter in pivot table keys

Question

I'm trying to filter using a dynamic key into PivotTable.

I have 3 tables, defined below. When I don't use group by, my query uses a LEFT JOIN statement and results in something like this:

_{(source: maverabilisim.com)}

Yellow bars are to-columns and the blue bar is the filter condition.

I need it to return something looking like this:

_{(source: maverabilisim.com)}

using a condition like this: .... AND v.key_id=1 (color=1)

How do I build SQL query to reach this result?

My SQL schema/test data:

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

Answer 1

Try

SELECT a.id, a.ads_title,
       MIN(CASE WHEN v.key_id = 1 THEN v.value END) `km`,
       MIN(CASE WHEN v.key_id = 3 THEN v.value END) `year`
  FROM ads_values v JOIN ads a
    ON v.ads_id = a.id 
 -- WHERE a.id = 2 -- if you need to fetch an ad with a particular id
 GROUP BY a.id, a.ads_title
HAVING SUM(CASE WHEN v.key_id = 4 AND v.value = 1 THEN 1 ELSE 0 END) > 0 

Sample output:

| ID | ADS_TITLE |    KM | YEAR |
---------------------------------
|  1 |       AAA | 89000 | 2010 |
|  2 |       BBB | 13000 | 2008 |
|  6 |       FFF | 45877 | 2009 |

Here is SQLFiddle demo