SQL syntax for complex search

Question

I'm trying to figure out how to format an SQL SELECT command that will do a complex search

I have a database with two tables.

Files
FileID
FilePath

Parameters
ParameterName
ParamaterValue
FileID

The goal is to search Parameters for multiple values and then only return FileIDs for those that have all the values.

These are media files so the parameters are things like format, video width, audio codec, etc... I want the user to be able to select as many of these fields as they want and then have it return files that match all of the parameters.

So for example I want to select all the files that have a Format of MKV, a VideoCodec of AVC and and AudioCodec of AAC.

I can't figure out how to do that. If I do a left join I can get it to work with one parameter, but as soon as add a second it fails. I assume I need to use nested joins but I'm not good enough with SQL to figure out exactly how to do that.

This is a local C++ app using SQLite3 if that matters.

Answer 1

You may use IN

SELECT *
FROM file
WHERE fileid IN (SELECT fileid FROM parameters WHERE name = 'name1' and value = 'value1') AND
      fileid IN (SELECT fileid FROM parameters WHERE name = 'name2' and value = 'value2') AND 
      fileid IN (SELECT fileid FROM parameters WHERE name = 'name3' and value = 'value3')

Answer 2

One method for doing this uses group by and having:

select fileid
from parameters p
where (name = 'name1' and value = 'value1') or
      (name = 'name2' and value = 'value2') or
      (name = 'name3' and value = 'value3')
group by fileid
having count(distinct name) = 3;

The "3" is the number of different parameters that you are looking for.