CODEWITHSUNDEEP
sqloracle-database
Unreal user
Unreal user

Reputation: 1135

sql query for multi valued attributes

I have resources each represented by a guid and they have attribute name-value pairs. I would like to query for resources which have the given attribute name value pairs.

So, suppose the table looks like: 

GUID                                      ATTR_SUBTYPE        ATTR_VAL 
63707829116544a38c5a508fcde031a4            location            US 
63707829116544a38c5a508fcde031a4            owner               himanshu       
44d5bf579d9f4b9a8c41429d08fc51de            password            welcome1 
44d5bf579d9f4b9a8c41429d08fc51de            host                retailHost       
c67d8f5d1a9b41428f029d55b79263e1            key                 random 
c67d8f5d1a9b41428f029d55b79263e1            role                admin

and I want all the resources with location as US and owner as olaf.

One possible query would be:

select guid from table where attr_subtype = 'location' and attr_value = ‘US' INTERSECT select guid from table where attr_subtype = 'owner' and attr_value = ‘himanshu';

There can be any number of attribute name value pairs in the query, so an additional intersection per pair in the query. I was wondering if we can construct a better query as intersection is expensive.

Upvotes: 1

Views: 700

Answers (3)

JBrooks
JBrooks

Reputation: 10013

Insert your targets into a temp table then join to it.

select t.guid 
from table as t 
join temp
on t.attr_subtype = temp.attr_subtype
and t.attr_value = temp.attr_value

Upvotes: 1

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

Assuming you don't have duplicate attributes per GUID you can achieve the desired result without a JOIN:

SELECT "GUID" FROM T
WHERE ( "ATTR_SUBTYPE" = 'location' AND "ATTR_VAL" = 'US' )
OR    ( "ATTR_SUBTYPE" = 'owner' AND "ATTR_VAL" = 'himanshu' )
GROUP BY "GUID"
HAVING COUNT(*) = 2 -- <-- keep only GUID have *both* attributes

See http://sqlfiddle.com/#!4/80900/2

Upvotes: 2

Sanders the Softwarer
Sanders the Softwarer

Reputation: 2496

Generally, JOIN would be better than INTERSECT here. It gives a chance to get first records prior than several full table scans will finish. But anyway you select a slow data structure so it wouldn't wonderful if it slowdown.

Try something like

select * 
from 
  (select * from table where attr_subtype = 'location' and attr_value = 'US') t1 
    join
  (select * from table where attr_subtype = 'owner' and attr_value = 'himanshu') t2
    on (t1.guid = t2.guid)
  ...

Upvotes: 1

Related Questions