Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table which has a list of all attributes that any object can have:

attr_id    attr_name    attr_data_type
------      -----        --------------

I have a table [object_key] in which each object_key_id has an unique list i.e set of attr_id,attr_value pairs.

object_key_id   attr_id   attr_value
---------       -------   ----------
1               1          'value1'
1               2          'value2'
2               1          'value3'
2               2          'value4'
3               1          'value1'
3               2          'value2'
3               3          'value3'

In this case, what is the best way to get the object_key_id for a given set of attr_id,attr_value pairs.

Ex: Input: [{1, 'value1'}, {2, 'value2'}] Output: 1

What's the best way to build a unique index i.e to have unique object_key_id for a set of attr_id,attr_value pairs?

share|improve this question
    
I don't see any row {1, 'aba'} and neither any row with {123, 'bcd'} in the data. Typos? –  ypercube May 21 at 9:37
    
Updated. check now.. –  Dumper May 21 at 17:31
    
So, only object 1 and not 3 because object 3 has also the {3, 'value3'} pair, right? –  ypercube May 21 at 17:37

2 Answers 2

What you want is called (exact) Relational Division.

See also this article: Divided We Stand: The SQL of Relational Division

There are several ways to have a query like that solves this problem. You can load the input data to a (temporary) table or use a CTE like this:

WITH input_data (attr_id, attr_value) AS
  ( SELECT *
    FROM 
      ( VALUES                              -- the input data. 
          (1, 'value1'), 
          (2, 'value2')
      ) AS i (attr_id, attr_value)
  ),
t AS                                        -- all the object_key_id values.
  ( SELECT DISTINCT object_key_id           -- if you have a separate table with those
    FROM tableX                             -- you can use it, instead. 
  )
SELECT object_key_id
FROM t
WHERE NOT EXISTS                            -- this subquery ensures
      ( SELECT attr_id, attr_value          -- that all input attribute/value pairs
        FROM input_data                     -- appear for an object_key
      EXCEPT
        SELECT attr_id, attr_value
        FROM tableX AS tt
        WHERE tt.object_key_id = t.object_key_id 
      ) 
  AND NOT EXISTS                            -- and this subquery does the reverse
       ( SELECT attr_id, attr_value         -- i.e. the exact part of the division
        FROM tableX AS tt
        WHERE tt.object_key_id = t.object_key_id 
      EXCEPT
        SELECT attr_id, attr_value
        FROM input_data
      ) ;

Test at SQL-Fiddle

This question at StackOverflow: How to filter SQL results in a has-many-through relation has a few more ways to solve it and benchmarks for Postgres but it's not for the exact variation, only for the "Division with Remainder". The queries will be similar but an extra check/condition has to be added for the exact variation.

share|improve this answer

Relational division is the proper way to do this.

If you know what attributes your objects can have you can construct a view based on a PIVOTED query. Experience shows this to be dog slow for non-trivial row counts, however.

Concatenating the attribute IDs and values, then hashing and storing an object / hash lookup has worked for me. You have to order consistently, of course, choose delimiters carefully and deal with hash collisions.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.