Optimizing tricky SQL search query

Question

I am trying to come up with a simple, performant query for the following problem:

Let's say there are several entities (items) which all have a unique ID. The entities have a variable set of attributes (properties), which therefore have been moved to a separate table:

T_Items_Props
=======================
Item_ID  Prop_ID  Value
-----------------------
101      1        'abc'
101      2        '123'
102      1        'xyz'
102      2        '123'
102      3        '102'
...      ...      ...

Now I want to search for an item, that matches some specified search-criteria, like this:

<<Pseudo-SQL>>

SELECT Item_Id(s)
  FROM T_Items_Props
 WHERE Prop 1 = 'abc'
   AND Prop 2 = '123'
     ...
   AND Prop n = ...

This would be fairly easy if I had a table like Items(Id, Prop_1, Prop_2, ..., Prop_n). Then I could do a simple SELECT where the search criteria could simply (even programmatically) be inserted in the WHERE-clause, but in this case I would have to do something like:

SELECT t1.Item_ID
  FROM T_Items_Props t1
     , T_Items_Props t2
     , ...
     , T_Items_Props tn -- (depending on how many properties to compare)
   AND t1.Item_ID = t2.Item_ID
   AND t1.Prop_ID = 1 AND t1.Value = 'abc'
   AND t2.Prop_ID = 2 AND t2.Value = '123'
     ...
   AND tn.Prop_ID = n AND tn.Value = ...

Is there a better/simpler/faster way to do this?

Answer 1

I've offered this in a previous post of similar querying intentions. The user could have 2 criteria one time, and five criteria another and wanted an easy way to build the SQL command. To simplify the need of having to add FROM tables and update the WHERE clause, you can simplify by doing joins and put that criteria right at the join level... So, each criteria is it's own set added to the mix.

SELECT 
      t1.Item_ID
   FROM 
      T_Items_Props t1

         JOIN T_Items_Props t2
            on t1.Item_ID = t2.Item_ID
           AND t2.Prop_ID = 2
           AND t2.Value = '123'

         JOIN T_Items_Props t3
            on t1.Item_ID = t3.Item_ID
           AND t3.Prop_ID = 6
           AND t3.Value = 'anything'

         JOIN T_Items_Props t4
            on t1.Item_ID = t4.Item_ID
           AND t4.Prop_ID = 15
           AND t4.Value = 'another value'
   WHERE
          t1.Prop_ID = 1
      AND t1.Value = 'abc'

Notice the primary query will always start with a minimum of the "T1" property/value criteria, but then, notice the JOIN clauses... they are virtually the same so it is very easy to implement via a loop... Just keep aliasing the T2, T3, T4... as needed. This will start with any items that meet the T1 criteria, but then also require all the rest to be found too.