mySQL多选两个表（过滤）

I have two tables at my DB that i want to make a select query First one keeps the product id (int) and if the product is published The second table keeps the filters, and matches with id_product (which is basicaly the product_id from Products) . at id_filter_child we have the value

products

product_id,
published

filters

id_pro_fil,
id_product,
id_filter_child

My tables now are:

products

1     yes
2     yes
3     no

filters

10   1    4
11   1    5
12   2    4
13   2    6
14   2    7

So that is my DB at the moment. From input i have the filters that i want to search So i want to select all products with filter=4 i use:

SELECT * FROM products,filters
WHERE products.product_id=filters.id_product
     AND products.published='1'
     AND filters.id_filter_child='4'

That one works!

Now i need to select all products with filter 4 AND 6. I use:

SELECT * FROM products,filters
WHERE products.product_id=filters.id_product
     AND products.published='1'
     AND filters.id_filter_child='4'
     AND filters.id_filter_child='6'

but i doesnt work (as a result i would want the product with id=2)

Some help please

Thanks in advance

If you want to select all products, then there is no reason to also include the filters. One approach is to use an aggregation and having clause:

SELECT p.*
FROM products p JOIN
     filters f
     ON p.product_id = f.id_product
WHERE p.published = '1'
GROUP BY p.product_id
HAVING SUM(f.id_filter_child = '4') > 0 AND
       SUM(r.id_filter_child = '6') > 0

This returns product that have both filter 4 and filter 6.

Note that I also fixed the query to use proper, explicit join syntax.

I like this method for "set-within-sets" subqueries, because it is quite general. You can add another filter to the HAVING clause with a similar condition. You could get everything with 4 but not 6 by changing the second > 0 to = 0.

Try this:

SELECT p.*
FROM products p
LEFT JOIN filters ON p.product_id = f.id_product
WHERE p.published='1' AND f.id_pro_fil IN (4,6)

Consider your logic:

 AND filters.id_filter_child='4'
 AND filters.id_filter_child='6'

You're requiring every filter_child in the database to have two DIFFERENT values at the same time. "this record must be a banana and a ferrari simutaneously"

You want an OR instead:

AND ((filter_child = '4') OR (filter_child = '6'))

(note the extra brackets), or more compactly:

AND (filter_child IN ('4', '6'))

--- comment followup

If you need both values to be attached to a record,t hen you'll have to use some extra logic:

SELECT *, COUNT(filters.id_filter_child IN ('4', '6')) AS count
FROM products,filters
WHERE products.product_id=filters.id_product
     AND products.published='1'
     AND filters.id_filter_child IN ('4', '6')
GROUP BY filters.id_filter_child
HAVING count = 2

The where returns only the records that have either 4 or 6 for filter_child - this will catch records that have at least ONE of those two values. The COUNT(..) + GROUP + HAVING business will then filter out all but those records that have BOTH of the values.

You can use OR or IN

SELECT * FROM products,filters 
  WHERE products.product_id=filters.id_product 
      AND  products.published='1' 
      AND (filters.id_filter_child='4' OR filters.id_filter_child='6')

SELECT * FROM products,filters 
  WHERE products.product_id=filters.id_product 
      AND  products.published='1' 
      AND filters.id_filter_child IN (4,6)

you should OR in between the two conditions:

SELECT * FROM products,filters
WHERE products.product_id=filters.id_product
     AND products.published='1'
     AND filters.id_filter_child='4'
     OR filters.id_filter_child='6'

but to make your sql smaller and simpler you can use IN operator also:

SELECT * FROM products,filters
WHERE products.product_id=filters.id_product
     AND products.published='1'
     AND filters.id_filter_child IN(4,6)