CODEWITHSUNDEEP
mysqlsqldatabase
thechrisroberts
thechrisroberts

Reputation: 39

MySQL query finding matches across multiple rows

I have two tables, Data(Name, dataID) and Attributes(Name, attributeID, dataID) with a one-to-many relationship. One dataID might be associated with many attributeID's.

What I want to do is run a query that finds all dataIDs that have a specific set of attributeIDs. I can't do:

SELECT dataID
FROM Attributes
WHERE dataID = 1 AND (attributeID = 1 OR attributeID = 2 OR attributeID = 3);

That would grab all dataID's with any one of those attributes, I want the dataID's that have all of those attributes.

Suggestions?

Still wrapping my head around queries using more than very basic selects.

Upvotes: 0

Views: 331

Answers (2)

user2989408
user2989408

Reputation: 3137

Though this a brute force solution, it will work using EXISTS. Waiting for a better solution.

SELECT a.dataID 
FROM DataID a WHERE a.dataID = 1 
    AND EXISTS (SELECT 1 FROM Attributes d WHERE d.DataID = 1 AND d.attributeID = 1)
    AND EXISTS (SELECT 1 FROM Attributes d WHERE d.DataID = 1 AND d.attributeID = 2)
    AND EXISTS (SELECT 1 FROM Attributes d WHERE d.DataID = 1 AND d.attributeID = 3)

A GROUP BY solution is possible.

SELECT dataID 
FROM Attributes 
WHERE dataID = 1 AND (attributeID = 1 OR attributeID = 2 OR attributeID = 3)
GROUP BY dataID
HAVING COUNT(*) = 3

Upvotes: 1

Daniel Sparing
Daniel Sparing

Reputation: 2173

As you need to read three different rows of the Attributes table, I suggest to use JOIN's to avoid subqueries.

SELECT a1.dataID
FROM
    Attributes a1
    JOIN Attributes a2 ON
        a1.dataID=a2.dataID
    JOIN Attributes a3 ON
        a2.dataID=a3.dataID
WHERE
    a1.dataID = 1 AND
    a1.attributeID = 1 AND
    a2.attributeID = 2 AND
    a3.attributeID = 3;

Upvotes: 1

Related Questions