SQL Query with two different filters

I have a page which shows all cars from DB. I have two filters , both are multiple select filter. For example

filter 1 – Color

Red , green , blue <– All these are checkbox ,can be selected multiple

filter 2 – brand

BMW, Honda , Hyundai <– All these are checkbox ,can be selected multiple

I have done below query

Select * from cars
JOIN term_car_relationships  
ON cars.id = term_cars_relationships.car_id 
 WHERE term_cars_relationships.term_id in (6,2,3) 
GROUP BY cars.id

In above query term_id

6 = blue (Color)

2 = green (Color)

3 = BNW (brand)

But with above query I will get all cars which has blue color or green color or BMW brand But how to change in such a way that I get BMW which is blue or green color.

I have 3 tables which handles these categories.

taxonomy table

taxonomy_id | taxonomy_title
1           | Color
2           | Brand

term_list

term_id  |  term_name  |  taxonomy_id
1        | Blue        | 1
2        | Red         | 1
3        | BMW         | 2
4        | Honda       | 2

term_cars_relationships Table

 term_id | car_id   
     1      |  1
     1      |  2
     2      |  3

Answer

You can construct your query with separate joins for each term category, and separate filter conditions for each as well.

SELECT cars.*, colorTerms.*, brandTerms.* 
FROM cars
INNER JOIN term_car_relationships AS carColorTerms
   ON cars.id = carColorTerms.car_id
INNER JOIN term_list AS colorTerms 
   ON carColorTerms.term_id = colorTerms.term_id AND colorTerms.taxonomy_id = 1
INNER JOIN term_car_relationships AS carBrandTerms
   ON cars.id = carBrandTerms.car_id
INNER JOIN term_list AS brandTerms 
   ON carBrandTerms.term_id = brandTerms.term_id AND brandTerms.taxonomy_id = 2
WHERE colorTerms.term_id IN (6, 2)
   AND brandTerms.term_id IN (3)

Of course, to construct this query, you will need to know the terms’ types before the query.

---

Also, using GROUP BY cars.id without aggregation is probably a sign of a problem, or just not the right way to get what you want. If you only want the information from the cars table, you should just SELECT DISTINCT cars.*. Using GROUP BY in this manner will end up with results with the data from just one color-brand match for each car.

---

With the complexity the edit to the original question added, another possibility presents itself….

SELECT cars.* -- You should really just select the fields you want, and may have to in some configurations (see GROUP BY)
FROM cars AS c
INNER JOIN term_car_relationships AS tcr ON c.car_id = tcr.car_id
INNER JOIN term_list AS tl ON tcr.term_id = tl.term_id
WHERE tcr.term_id IN (6, 2, 3)
GROUP BY cars.car_id -- In some configurations of MySQL, and most other RDBMSes, you must specify every SELECTed non-aggregated field here
HAVING COUNT(DISTINCT tl.taxonomy_id) 
   = ( SELECT COUNT(DISTINCT taxonomy_id) 
       FROM term_list 
       WHERE term_id IN (6, 2, 3)
     )

Note: This final solution does not actually require you to know term taxonomies ahead of time anymore, and does not grow as more taxonomies need supported; so while it is a little less obvious with what it is doing, is probably definitely worth considering.