Select only products that are active via query from two tables

I have a query that provides me with a list of productId:s that I then match with a products table.. But how would I adjust the query to give me only productId:s that are active (active=1)?

My query:

SELECT productId
FROM prod_rel
GROUP BY productId
HAVING SUM(carId = 12) > 0 AND SUM(objectiveId = 15) > 0 AND SUM(locationId = 11) > 0;

Table products
+----+-----------+------------+-------------+
| id | product   | name       | active      |
+----+-----------+------------+-------------+
|  1 |         1 |        Jeep|           1 |     
|  2 |         1 |       Tesla|           0 |   
|  3 |         1 |        Audi|           1 |    
|  4 |         2 |    Mercedes|           1 |   
|  5 |         2 |      Volvo |           0 |      
|  6 |         2 |        BMW |           0 |    
+----+-----------+------------+-------------+

Table: prod_rel
+----+-----------+------------+-------------+------------+
| id | productId | carId      | objectiveId | locationId |
+----+-----------+------------+-------------+------------+
|  1 |         1 |         12 |           0 |          0 |
|  2 |         1 |          0 |          15 |          0 |
|  3 |         1 |          0 |           0 |         11 |
|  4 |         2 |         11 |           0 |          0 |
|  5 |         2 |          0 |          10 |          0 |
|  6 |         2 |          0 |           0 |         14 |
+----+-----------+------------+-------------+------------+

Answer

You can use join:

SELECT pr.productId
FROM prod_rel pr JOIN
     product p
     ON pr.productId = p.id
WHERE p.active = 1
GROUP BY pr.productId
HAVING SUM(carId = 12) > 0 AND SUM(objectiveId = 15) > 0 AND SUM(locationId = 11) > 0;

Advertisement

Answer