Querying based on a set of Named Attributes/Values

Question

I am working with a set of what is essentially Attribute/Value pairs (there's actually quite a bit more to this, but I'm simplifying for the sake of this question). Effectively you can think of the tables as such:

Entities (EntityID,AttributeName,AttributeValue) PK=EntityID,AttributeName
Targets (TargetID,AttributeName,AttributeValue) PK=TargetID,AttributeName

How would you query with SQL the set of EntityID,TargetID for which an Entity has all the attributes for a target as well as the corresponding value?

EDIT (DDL as requested):

CREATE TABLE Entities(
    EntityID INTEGER NOT NULL,
    AttributeName CHAR(50) NOT NULL,
    AttributeValue CHAR(50) NOT NULL,
    CONSTRAINT EntitiesPK PRIMARY KEY (EntityID,AttributeName)
);
CREATE TABLE Targets(
    TargetID INTEGER NOT NULL,
    AttributeName CHAR(50) NOT NULL,
    AttributeValue CHAR(50) NOT NULL,
    CONSTRAINT TargetsPK PRIMARY KEY (TargetID,AttributeName)
);

score 0 · Answer 1 · answered Jan 16 '09 at 20:38

0

I like these kind of questions but I think it is not unreasonable to hope that the OP provides at least create scripts for the table(s) and maybe even some sample data.

I like to hear who agrees and who disagrees.

answered Jan 16 '09 at 20:38

tuinstoel

7,248
27
27

I agree, that would be nice. But in only about 1 in 20 database questions do I see people provide DDL and sample data. Alas. – Bill Karwin Jan 16 '09 at 20:39
I added DDL for the two example tables I proposed – Mark Roddy Jan 16 '09 at 20:43

Bill Karwin · Accepted Answer · 2009-01-19T20:25:41.080

Okay, I think after several tries and edits, this solution finally works:

SELECT e1.EntityID, t1.TargetID
FROM Entities e1
  JOIN Entities e2 ON (e1.EntityID = e2.EntityID)
  CROSS JOIN Targets t1
  LEFT OUTER JOIN Targets t2 ON (t1.TargetID = t2.TargetID
    AND e2.AttributeName = t2.AttributeName
    AND e2.AttributeValue = t2.AttributeValue)
GROUP BY e1.EntityID, t1.TargetID
HAVING COUNT(e2.AttributeValue) = COUNT(t2.AttributeValue);

Test data:

INSERT INTO Entities VALUES 
 -- exact same attributes, should match
 (1, 'Foo1', '123'),
 (1, 'Bar1', '123'),
 -- same attributes but different values, should not match
 (2, 'Foo2', '456'),
 (2, 'Bar2', '456'),
 -- more columns in Entities, should not match
 (3, 'Foo3', '789'),
 (3, 'Bar3', '789'),
 (3, 'Baz3', '789'),
 -- fewer columns in Entities, should match
 (4, 'Foo4', '012'),
 (4, 'Bar4', '012'),
 -- same as case 1, should match Target 1
 (5, 'Foo1', '123'),
 (5, 'Bar1', '123'),
 -- one attribute with different value, should not match
 (6, 'A', 'one'),
 (6, 'B', 'two');

INSERT INTO Targets VALUES 
 (1, 'Foo1', '123'),
 (1, 'Bar1', '123'),
 (2, 'Foo2', 'abc'),
 (2, 'Bar2', 'abc'),
 (3, 'Foo3', '789'),
 (3, 'Bar3', '789'),
 (4, 'Foo4', '012'),
 (4, 'Bar4', '012'),
 (4, 'Baz4', '012'),
 (6, 'A', 'one'),
 (6, 'B', 'twox');

Test results:

+----------+----------+
| EntityID | TargetID |
+----------+----------+
|        1 |        1 | 
|        4 |        4 | 
|        5 |        1 | 
+----------+----------+

To respond to your comment, here is a query with the tables reversed:

SELECT e1.EntityID, t1.TargetID
FROM Targets t1
  JOIN Targets t2 ON (t1.TargetID = t2.TargetID)
  CROSS JOIN Entities e1
  LEFT OUTER JOIN Entities e2 ON (e1.EntityID = e2.EntityID
    AND t2.AttributeName = e2.AttributeName
    AND t2.AttributeValue = e2.AttributeValue)
GROUP BY e1.EntityID, t1.TargetID
HAVING COUNT(e2.AttributeValue) = COUNT(t2.AttributeValue);

And here's the output, given the same input data above.

+----------+----------+
| EntityID | TargetID |
+----------+----------+
|        1 |        1 |
|        3 |        3 |
|        5 |        1 |
+----------+----------+

Is that solution what is asked? Test it with insert into entities values ( 2, 'A', 'one'); insert into entities values ( 2, 'B', 'two'); insert into targets values ( 2, 'A', 'one'); insert into targets values ( 2, 'B', 'twox'); — tuinstoel, Jan 16 '09 at 21:45
You're right, you can't group t.TargetID's together when some may be NULL because of the outer join. I have changed the query, and I'll include test data and results. — Bill Karwin, Jan 16 '09 at 22:38
Bill, this is actually backwards. An entity can have more attributes then specified by the target but must have all the attributes specified by the target. For example data, Entity 3 matches Target 3 as it has all the criteria of the target and Entity 4 shouldn't match as it doesn't have 'Baz4'. — Mark Roddy, Jan 17 '09 at 00:08

score 0 · Answer 3 · answered Jan 16 '09 at 21:18

0

SELECT  *
FROM    (
    SELECT  eo.total,
        (
        SELECT  COUNT(*)
        FROM    Entities e, Targets t
        WHERE   e.EntityID = eo.EntityID
            AND t.TargetID = e.EntityID
            AND t.AttributeName = e.AttributeName
            AND t.AttributeValue = e.AttributeValue
        ) AS equal
    FROM    (
        SELECT  e.EntityID, COUNT(*) as total
        FROM    Entities e
        GROUP BY
            e.EntityID
        ) eo
    )
WHERE   total = equal

answered Jan 16 '09 at 21:18

Quassnoi

413,100
91
616
614

Why do you assume that t.targetid = e.entityid? I think you can drop that. – tuinstoel Jan 16 '09 at 21:30
Target and Entity id's are unrelated – Mark Roddy Jan 17 '09 at 00:09

tuinstoel · Answer 4 · 2009-01-18T21:34:27.493

select distinct entityid,targetid
from   entities ent
,      targets  tar
where  not exists  
       (  select attributename, AttributeValue 
          from   targets  tar2
          where  tar.targetid = tar2.targetid
          minus
          select attributename, AttributeValue 
          from   entities  ent2
          where  ent2.entityid = ent.entityid)
and    not exists  
       (  select attributename, AttributeValue 
          from   entities  ent2
          where  ent2.entityid = ent.entityid
          minus 
          select attributename, AttributeValue 
          from   targets  tar2
          where  tar.targetid = tar2.targetid)
order by entityid,targetid
/

edit1:

If it is OK to have rows in the target table that have no match in the entities table, the solution simplifies to:

select distinct entityid,targetid
from   entities ent
,      targets  tar
where  not exists  
       (  select attributename, AttributeValue 
          from   entities  ent2
          where  ent2.entityid = ent.entityid
          minus 
          select attributename, AttributeValue 
          from   targets  tar2
          where  tar.targetid = tar2.targetid)
order by entityid,targetid
/

edit 2:

It is not easy to understand the exact requirements of the OP.

Here is a new select statement. I hope he will test all my select statements to understand the differences. I hope he has good test cases and knows what he wants.

select distinct entityid,targetid
from   entities ent
,      targets  tar
where  not exists  
       (  select attributename, AttributeValue 
          from   targets  tar2
          where  tar.targetid = tar2.targetid
          minus  
          select attributename, AttributeValue 
          from   entities  ent2
          where  ent2.entityid = ent.entityid)
order by entityid,targetid
/

Oracle does, I believe it is called 'except' in Sql Server. The question is tagged with Oracle by the OP. — tuinstoel, Jan 16 '09 at 23:00

Querying based on a set of Named Attributes/Values

4 Answers4

Linked