mysql - filter in pivot table keys -

i'm trying filter using dynamic key pivottable.

i have 3 tables, defined below. when don't use group by, query uses left join statement , results in this:

example 1 http://maverabilisim.com/temp/issue1.jpg

yellow bars to-columns , blue bar filter condition.

i need return looking this:

example 2 http://maverabilisim.com/temp/issue2.jpg

using condition this: .... , v.key_id=1 (color=1)

how build sql query reach 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 `ads` values (1, 'aaa'); insert `ads` values (2, 'bbb'); insert `ads` values (3, 'ccc'); insert `ads` values (4, 'ddd'); insert `ads` values (5, 'eee'); insert `ads` values (6, 'fff'); insert `ads_keys` values (1, 'km', 1); insert `ads_keys` values (2, 'oil', 0); insert `ads_keys` values (3, 'year', 1); insert `ads_keys` values (4, 'color', 0); insert `ads_keys` values (5, 'speed', 0);  insert `ads_values` values (1, 1, 1, 89000); insert `ads_values` values (2, 1, 2, 200); insert `ads_values` values (3, 1, 3, 2010); insert `ads_values` values (4, 1, 4, 1); insert `ads_values` values (5, 1, 5, 180); insert `ads_values` values (6, 2, 1, 13000); insert `ads_values` values (7, 2, 2, 150); insert `ads_values` values (8, 2, 3, 2008); insert `ads_values` values (9, 2, 4, 1); insert `ads_values` values (10, 2, 5, 160); insert `ads_values` values (11, 3, 1, 79800); insert `ads_values` values (12, 3, 2, 172); insert `ads_values` values (13, 3, 3, 2008); insert `ads_values` values (14, 3, 4, 2); insert `ads_values` values (15, 3, 5, 178); insert `ads_values` values (16, 4, 1, 56781); insert `ads_values` values (17, 4, 2, 127); insert `ads_values` values (18, 4, 3, 2009); insert `ads_values` values (19, 4, 4, 3); insert `ads_values` values (20, 4, 5, 156); insert `ads_values` values (21, 5, 1, 10200); insert `ads_values` values (22, 5, 2, 205); insert `ads_values` values (23, 5, 3, 2000); insert `ads_values` values (24, 5, 4, 3); insert `ads_values` values (25, 5, 5, 160); insert `ads_values` values (26, 6, 1, 45877); insert `ads_values` values (27, 6, 2, 150); insert `ads_values` values (28, 6, 3, 2009); insert `ads_values` values (29, 6, 4, 1); insert `ads_values` values (30, 6, 5, 168); 

try

select a.id, a.ads_title,        min(case when v.key_id = 1 v.value end) `km`,        min(case when v.key_id = 3 v.value end) `year`   ads_values v join ads     on v.ads_id = a.id   -- a.id = 2 -- if need fetch ad particular id  group a.id, a.ads_title having sum(case when v.key_id = 4 , v.value = 1 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 sqlfiddle demo