Search code examples
mysqlsqlhaving

How to make sure results will contain at least one ID?

The following query is collecting content IDs from a relational table. It is making sure that my result has at least 2 keywords matching.

My problem is that I also have a "hard keyword" (let's say ID 127), meaning I need to make sure that the hard keyword is included as a must. I guess I'd need to modify my having clause, but I cannot figure out how.

select * from `contents__keywords`
where `keyword_id` in (127, 162, 249, 567)
group by `content_id`
having count(distinct `keyword_id`) >= 2
order by `content_id`
desc
limit 4
Solution

  • You can add a conditional condition:

    having count(distinct `keyword_id`) >= 2 and
       sum(case when keyword_id = 127 then 1 else 0 end) > 0

    If you are using MySQL (as the backticks suggest), you can use a shortcut:

    having count(distinct `keyword_id`) >= 2 and
       sum( keyword_id = 127 ) > 0