INNER JOIN pick id if two(!) keywords are in any of the returned rows-CodePudding

I have two tables in my mysql database. The first table has the columns "id" and "text", the second table has the columns "text_id" and "keyword" which contais a single keyword belonging to the text with the given ID. Column "text_id" of the second table is always equal to the according id in table 1. Table two can contain multiple rows with the same "text_id" and different "keyword"s.

I want to filter those articles for their linked keywords. For example SELECT * FROM table1 INNER JOIN table2 ON table2.text_id = table1.id WHERE table1.id = "1" contains a list with 5 different keywords. Now I want to only recieve the data if two (or more) given keywords are in any of those rows.

However WHERE table2.keyword ="XXX" AND table2.keyword = "YYY" obviously does not return anything because one cell cannot be equal to two different values. On the other hand WHERE table2.keyword ="XXX" OR table2.keyword = "YYY" returns the data where only one and not neccessarily both values are included.

Is there any way to achieve this via a mysql query? Or do I have to make different queries for the desired keywords and compare their returned IDs later on?

CodePudding user response：

As far as I know you need to do one of the two things:

JOIN table2 two (or more) times, once for every keyword

SELECT *
  FROM table1
    INNER JOIN table2 AS xxx ON table1.id = xxx.text_id
    INNER JOIN table2 yyy ON table1.id = yyy.text_id
  WHERE xxx.keyword = "XXX"
    AND yyy.keyword = "YYY"

OR use aggregation function GROUP_CONCAT, and search in the GROUPED field using HAVING. However this approach has two major drawbacks - GROUP_CONCAT field length is limited and using HAVING is slower than using WHERE (it searches "later" for the requested value in execution of the query).