SQL with AND in the same column

Question

I have a 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 will create his own query in SQL, it will look like this :

SELECT file FROM values WHERE (metadata='duration' AND value='20s') AND (metadata='extension' AND value='mkv')

I know this query is bad, but I can't change the 'values' table. I don't know how to get the file_id with these conditions..

Any ideas ?

Thanks in advance !

Answer 1

Like this:

SELECT file
FROM
(
  SELECT file,
    MAX(CASE WHEN metadata = 'duration' THEN value END) AS duration,
    MAX(CASE WHEN metadata = 'extension' THEN value END) AS extension
  FROM `values`
  WHERE metadata IN ('duration', 'extension') 
  GROUP BY file
) AS sub
WHERE duration = '20s' AND extension = 'mkv';

See it in action here:

• SQL Fiddle Demo

---

Update

If you want to do this dynamically, and assuming that these metadata names are stored in a new separate table, then you can use the dynamic sql to do this. Something like this:

SET @sql = NULL;
SET @cols = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(m.metadata_name = ''',
      m.metadata_name, ''', v.value, 0)) AS ', '''',   m.metadata_name, '''')
  ) INTO @cols
FROM Metadata AS m;

SET @sql = CONCAT('
  SELECT 
    v.file, ', @cols ,'
  FROM `values` AS v
  INNER JOIN metadata AS m ON v.metadata_id = m.metadata_id
  GROUP BY v.file');

prepare stmt 
FROM @sql;

execute stmt;

Updated SQL Fiddle Demo

Then you can put this inside a stored procedure and use it to display them, or query them the way you want.

Answer 2

try this

SELECT file FROM `values` 
WHERE (metadata='duration'  AND value='20s') 
OR    (metadata='extension' AND value='mkv')

• VALUES is reserved key word for mysql , use backticks around it

EDIT:

you should have table like that

 file | size | duration | extension |
  1   | 150mo|  50s     | avi
  2   | null |  20s     | mkv
  3   | null |  20s     | mpeg

then you query will be

    select file from `values` where duration = '20s' and extension = 'mkv'