Dan Dan - 2 months ago 16
SQL Question

SQL syntax for complex search

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 Source

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.