SQL query filter down to only applicable rows

How would I select a query like this? I am a bit confused because this would return nothing since any of the rows do not fulfill any of the “where clauses”..

What I would like is the answer to the query to produce the count of 1. And that the productId is “1”… Thanks for help!

SELECT productId where carId=12 and objectiveId=15 and locationId=11

+----+-----------+------------+-------------+------------+
| 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

One approach here would be to aggregate by product and then assert the presence of the various IDs:

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

For an explanation to the above, the HAVING clause evaluates after the GROUP BY aggregation has taken place. That is, whatever logic which you see in the HAVING clause deals with the groups of records for each product (and not the individual product records themselves). The expression SUM(carId = 12) basically counts the number of times for which cardId = 12. You want this condition to be true at least once, for each matching product (as well as the other two conditions).