vendredi 24 juin 2016

Filter by multiple items with join between two tables


I have a table posts like that:

enter image description here

and another table post_filters in witch I have stored the filter for each post:

enter image description here

where:

enter image description here

enter image description here

I want all the apples with

  • color red or Yellow and
  • from the country England and France.

How can I do that with join between posts and posts_filters? It is possible to do only one join between the two tables ?

I've tried something like that:

SELECT   
    posts.*,    
    a1.*,  
    a2.*  
FROM posts  
    LEFT JOIN post_filters a1 ON posts.ID=a1.post_id AND a1.filter_id=1   
    LEFT JOIN post_filters a2 ON posts.ID=a2.post_id AND a2.filter_id=2      
WHERE   
a1.selected_filter_item IN (2,3)   
AND   
a2.selected_filter_item IN (1,3)

It doesn't work proprierly and I would like to have only ONE join.


Aucun commentaire:

Enregistrer un commentaire