CODEWITHSUNDEEP
mysqlsqlconditional-statements
Kib'
Kib'

Reputation: 178

SQL with AND in the same column

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 !

Upvotes: 3

Views: 109

Answers (5)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

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:

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.

Upvotes: 7

echo_Me
echo_Me

Reputation: 37233

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'

Upvotes: 2

mojtaba
mojtaba

Reputation: 339

using exists for 2th condition : 

SELECT distinct file FROM values WHERE (metadata='duration' AND value='20s') AND
exists (  SELECT * FROM values as v2 where v2.metadata='extension' AND v2.value='mkv' and v2.file=values.file )

Upvotes: 0

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

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

Upvotes: 0

Patric
Patric

Reputation: 2989

It is not exactly clear what you want to achieve. I guess you want all results that are either "duration" & "20s" or "extensions" and "mkv". So you should just replace or middle "AND" with "OR".

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

Upvotes: 0

Related Questions