mysql - SQL with AND in the same column -

i have mysql table named 'values' :

values:  file | metadata | value ________________________ 01   | duration | 50s 01   | size     | 150mo 01   | extension| avi 02   | duration | 20s 02   | extension| mkv 03   | duration | 20s 03   | extension| mpeg 

an user create own query in sql, :

select file values (metadata='duration' , value='20s') , (metadata='extension' , value='mkv') 

i know query bad, can't change 'values' table. don't know how file_id these conditions..

any ideas ?

thanks in advance !

like this:

select file (   select file,     max(case when metadata = 'duration' value end) duration,     max(case when metadata = 'extension' value end) extension   `values`   metadata in ('duration', 'extension')    group file ) sub duration = '20s' , extension = 'mkv'; 

see in action here:

• sql fiddle demo

---

update

if want dynamically, , assuming these metadata names stored in new separate table, can use dynamic sql this. this:

set @sql = null; set @cols = null;  select   group_concat(distinct concat('max(if(m.metadata_name = ''',       m.metadata_name, ''', v.value, 0)) ', '''',   m.metadata_name, '''')   ) @cols metadata m;  set @sql = concat('   select      v.file, ', @cols ,'   `values` v   inner join metadata m on v.metadata_id = m.metadata_id   group v.file');  prepare stmt  @sql;  execute stmt; 

updated sql fiddle demo

then can put inside stored procedure , use display them, or query them way want.