Prequisites

There are two tables in my file (it's about vinyl records):

Records

Styles

(Styles.style is stored as an Integer but a String is used for simplicity here)

Following condition is set for table Styles:
Styles.recordId = Records.ID for every given vinyl record

->This is necessary to find all styles related to one specific vinyl record

Therefore, there are two vinyls stored:

Title_1 has two styles Blues, Disco
Title_2 has two styles Blues, Electro

Question

In my SELECT query, I want to find vinyl records with a certain style

Find Titles with one style

If I want to find a vinyl record with one specific style, it seems pretty straight forward.
I use:

SELECT Records.Title
FROM Records
JOIN Styles ON Styles.recordId = Records.ID
WHERE Styles.style=Disco

With the desired result:

(Title_1)

Find Titles with more styles

Let`s say, I want to find all titles with the styles (Blues AND Electro)
Using the statement from before and just adding another statement does not seem to work:

SELECT Records.Title
FROM Records
JOIN Styles ON Styles.recordId = Records.ID
WHERE Styles.style=Blues AND Styles.style=Electro

This does not work as the column Styles.style never consists of both styles at the same time.

How can I tell SQLite to return all titles that satisfy the requirement? The output should be

(Title_2)

EDIT: Fixed tables not rendering

CodePudding user response：

For this you need to filter the rows of your resultset for the styles that you want and aggregation with the condition in the HAVING clause that for a title both styles exist:

SELECT r.id, r.Title
FROM Records r JOIN Styles s 
ON s.recordId = r.ID
WHERE s.style IN ('Blues', 'Electro')
GROUP BY r.id, r.Title
HAVING COUNT(*) = 2;

I assume that in the table style the combination of the columns recordId and style is unique.