mysql - PHP - SQL Filter category in one query

Question

I have 3 table in my database: Categories, Category_relationships and Article.

Category_relationships is like

id | category | article
---+----------+--------
1  |     2    |   3
2  |     4    |   3

Im getting a url like this: filter.php?category[]=2&category[]=4

I want to list article number 3 with one SQL query.
How can I do that?

And sorry for my English :)

Answer 1

There could be multiple approaches to get the desired results, Following queries are based on to match if article has these 2 categories, if there are 3 categories and you need apply matching criteria 3 times with different category id for each match

Using aggregation

select a.id, a.name
from article a
join category_relationships cr on a.id = cr.article_id
join category c on c.id = cr.category_id
group by  a.id, a.name
having count(case when c.id = 2 then 1 else null) > 0
and count(case when c.id = 4 then 1 else null) > 0

or

select a.id, a.name
from article a
join category_relationships cr on a.id = cr.article_id
join category c on c.id = cr.category_id
where c.id in(2,4)
group by a.id, a.name
having count(c.id) = 2 

Here 2 is variable depends upon how many categories to match, Also if there is chance of duplicates like there are more than one entries per article with same category id then use having count(distinct c.id) = 2

Using EXISTS

select a.id, a.name
from article a
where exists(
    select 1
    from category_relationships 
    where a.id = article_id
    and category_id = 2
) and exists(
    select 1
    from category_relationships 
    where a.id = article_id
    and category_id = 4
)