SQL select query inner combines many of the many refactors

Question

I have many, many relationships between entries and keywords with a join entries_keywords table. I want to get all the entries both for "waking up" the keys and for "up". The only way I came up with this. If I want to throw in another search word, it gets even worse. How do you reorganize this? Is there any other way to join it than using subqueries?

select * from ( select * from entries e inner join entries_keywords ek on e.id = ek.entry_id inner join keywords k on ek.keyword_id = k.id where k.key = 'wake' ) e inner join entries_keywords ek on e.id = ek.entry_id inner join keywords k on ek.keyword_id = k.id where k.key = 'up'; 

Answer 1

If you want to receive records containing at least one of the keywords, you can do:

 SELECT a.* FROM entries a INNER JOIN entries_keywords b ON a.id = b.entry_id INNER JOIN keywords c ON b.keyword_id = c.id WHERE c.key IN ('wake', 'up') 

If you want to get entries that contain ALL keywords in the list, add GROUP BY and HAVING :

 SELECT a.* FROM entries a INNER JOIN entries_keywords b ON a.id = b.entry_id INNER JOIN keywords c ON b.keyword_id = c.id WHERE c.key IN ('wake', 'up') GROUP BY a.id HAVING COUNT(*) = 2 

Where 2 is the number of keywords in the list that you are checking.

Answer 2

If I understand correctly, you can use UNION to combine two queries into a list:

 SELECT * FROM entries e INNER JOIN entries_keyworkds ek ON e.id = ek.entry_id INNER JOIN keywords k ON ek.keywork_id = k.id WHERE k.key = 'WAKE' UNION SELECT * FROM entries e INNER JOIN entries_keyworkds ek ON e.id = ek.entry_id INNER JOIN keywords k ON ek.keywork_id = k.id WHERE k.key = 'up' ; 

This will return all entries for waking up + all entries for up.

To find entries with both keys, use INTERSECT :

 SELECT * FROM entries e INNER JOIN entries_keyworkds ek ON e.id = ek.entry_id INNER JOIN keywords k ON ek.keywork_id = k.id WHERE k.key = 'WAKE' INTERSECT SELECT * FROM entries e INNER JOIN entries_keyworkds ek ON e.id = ek.entry_id INNER JOIN keywords k ON ek.keywork_id = k.id WHERE k.key = 'up' ; 

This will return all entries containing both keywords.