Question 174916 in Mysql Question posted in
The official MySQL documentation can be found here.

How can I rewrite this?

MySQL / MariaDB structure:

Person

| id |
|----|
| 1  |
| 2  |
| 3  |

Car
| person_id | car   |
|-----------|-------|
| 1         | Ford  |
| 2         | Mazda |
| 3         | Ford  |
| 3         | Mazda |
| 3         | BMW   |

What query can get the Persons with exactly 1 car, and car is not Mazda ?

expected output is person id 1.

Note:
if GROUP BY is used, query should be full group by, otherwise getting the following error, and changing the SQL configuration is not wanted.
"In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column ‘X’; this is incompatible with sql_mode=only_full_group_by"

Tags:

2

Answers


  1. 0

    You can use aggregation and having to filter:

    select person_id
from car
group by person_id
having count(*) = 1              -- has just one car
   and max(car = 'Mazda') = 0    -- which is not a Mazda

    We can get the result directly from the car table. If for some reason you need to bring the corresponding row from person, we can join:

    select p.*
from person p
inner join (
    select person_id
    from car
    group by person_id
    having count(*) = 1 and max(car = 'Mazda') = 0
) c on c.person_id = p.id
    Login or Signup to reply.
  2. 0

    Another option, use the negate of the EXISTS operator on a correlated subquery as follows:

    select * -- you may replace * with your columns list
from Car t1 
where car <> 'Mazda' and
  not exists(
  select 1 from Car t2 
  where t1.person_id = t2.person_id and
        t1.car <> t2.car)

    demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search