CODEWITHSUNDEEP
sqlsql-server
derdida
derdida

Reputation: 14904

SQL Categories with JOIN - multiple Select

I have the following setup:

  1. 1 table with news, every news item has categories
  2. 1 table with categories, for example "cars" and "boats"
  3. 1 table to connect news with categories

Now ill have an array of categories, and ill want to show the items from table 1 (news for example) that fits to ALL categories. So if ill have ID 35 and ID 36, there must be an entry for both IDs in the connection table.

For example: 

SELECT * 
FROM inserate 
INNER JOIN rubrikenZuordnungen ON inserate.ID = rubrikenZuordnungen.InseratID
WHERE 
    (rubrikenZuordnungen.RubrikID = 35) AND 
    (rubrikenZuordnungen.RubrikID = 36)
     OFFSET 10 ROWS
     FETCH NEXT 20 ROWS ONLY

If I'll use that, it's not working because I'll get only 1 result for rubrikenZuordnungen.

Can anyone help me out? Thanks in advance.

Upvotes: 1

Views: 42

Answers (1)

Martin Smith
Martin Smith

Reputation: 452978

This is one way.

SELECT *
FROM inserate
WHERE ID IN
(
SELECT InseratID
FROM rubrikenZuordnungen
WHERE RubrikID IN (35,36)
GROUP BY InseratID
HAVING COUNT(DISTINCT RubrikID)=2
)

Upvotes: 2

Related Questions