Geek Answers Handbook

MySQL one-to-many mapping query - if there is a match on many, do not return one

Let's say I have a table like this:

+----------+--------+
| brand    | vehicle|
+----------+--------+
|  ford    |    car | 
|  ford    |  truck | 
|  ford    |    suv | 
+----------+--------+

I want to write one query that returns a brand when all 3 rows satisfy the conditions. For example, if I say, “give me a brand that has a car, but not a truck or suv,” I expect the request to return nothing, since “ford” has all 3. Unfortunately, the request I built does not work : / p>

SELECT brand 
  FROM table 
 WHERE vehicle='car' 
   AND vehicle != 'truck' 
   AND vehicle != 'suv';

The request returns "ford". I understand why he did it. He tested the conditions for each row, and row 1 passed, so the brand from row 1 is returned. But how can I build a query to accomplish what I need?

+4
2

, , , HAVING.

SELECT brand 
  FROM table_name 
 GROUP BY brand
HAVING MAX(vehicle = 'car') = 1 
   AND MAX(vehicle = 'truck') = 0
   AND MAX(vehicle = 'suv') = 0;

SQLFiddle

+1

SELECT t1.brand
FROM table t1
LEFT JOIN table t2 ON (t1.brand = t2.brand AND t2.vehicle = 'truck')
LEFT JOIN table t3 ON (t1.brand = t3.brand AND t3.vehicle = 'suv')
WHERE t1.vehicle = 'car' AND t2.brand IS NULL AND t3.brand IS NULL

http://sqlfiddle.com/#!2/c5a06/3

+2

More articles:


All Articles