CODEWITHSUNDEEP
databasesqlite
Vinay Jain
Vinay Jain

Reputation: 2634

find records with an exact number of children

Database structure:

Restaurant

Retsau_Attribute

Attributes

I want to extract all the restaurant IDs that have all the attributes specified in the query.

I tried to use the IN operator, but it returns all restaurant IDs that have some or all of the attributes.

I used this query:

select rID from Retsau_Attribute where aID IN (a1, a2, a3);

I need only those restaurant IDs that have all the attributes specified (a1, a2, a3).

Upvotes: 0

Views: 29

Answers (1)

CL.
CL.

Reputation: 180210

Use grouping so that you can count the attributes per group:

SELECT rID
FROM Retsau_Attribute
WHERE aID IN (a1, a2, a3)
GROUP BY rID
HAVING COUNT(*) = 3

Upvotes: 2

Related Questions