How to select rows where an attribute can have two values

Question

I have a table called site_attributes which looks similar to this:

siteId     attributeId     tier
-------------------------------
site1id    attribute1id    1
site1id    attribute2id    1
site1id    attribute3id    1

How can I select from the table the unique site id where attributeId is equal to attribute1id AND attributeId is equal to attribute3id?

Barmar · Accepted Answer · 2012-11-03 16:39:40Z

2

SELECT siteId, count(*) ct
FROM TableName
WHERE attributeId IN ('attribute1id', 'attribute3id')
GROUP BY siteId
HAVING ct = 2

or

SELECT t1.siteId
FROM TableName t1
JOIN TableName t2 USING (siteId)
WHERE t1.attributeId = 'attribute1id'
AND t2.attributeId = 'attribute3id'

answered Nov 3, 2012 at 16:39

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ahmadov Over a year ago

Your first query might not work correctly if site1id has 2 attributeId with the same value. That is, if attribute3id was attribute1id it would still output site1id which is not what the user wants. However, your 2nd query should produce correct results.

Barmar Over a year ago

@Ahmedov Typically, relation tables like this are designed with (siteId, attributeId) being a unique, composite key, and I was making that simplifying assumption. And it's likely to be more efficient than a join.

Collectives™ on Stack Overflow

How to select rows where an attribute can have two values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related