Magnus Magnus - 1 month ago 5
SQL Question

Struggling with an IN oracle query

This is what my code looks like right now.

SELECT TITTEL, ISBN
FROM book b, subject s
WHERE b.ISBN = s.BOK AND s.sub IN('Database','Programming') ORDER BY b.year DESC;


With this query I find every book(bok) with the subject Database or Programming.

Kong Olav 123
Baser og enda mer baser 333
Baser og enda mer baser 333


But I want to find the books that have BOTH database and programming as its subject. How can I do this?

Answer

You are close. You can now use aggregation:

SELECT TITTEL, ISBN
FROM book b JOIN
     subject s
     ON b.ISBN = s.BOK 
WHERE s.sub IN ('Database', 'Programming')
GROUP BY TITTEL, ISBN
HAVING COUNT(DISTINCT s.sub) = 2
ORDER BY MAX(YEAR) DESC;
Comments