/*
 cars                   cars_location
+------+---------+    +-------------+-----+            garages
| id   | int     |<---| car_id        | int |     +-------+---------+
| name | varchar |    | garage_id     | int | --->| id    | int     |
+------+---------+    +-------------+-----+       | name  | varchar |
                                                  +-------+---------+
* /
drop table if exists cars, garages, cars_location;
create table cars
(
    id   int not null,
    name varchar(255),
    primary key (id)
);
create table garages
(
    id   int not null,
    name varchar(255),
    primary key (id)
);
create table cars_location
(
    car_id     int not null,
    garage_id  int not null,
    primary key (car_id, garage_id)
);
insert into cars (id, name)
values (1, 'BMW'),
       (2, 'Audi'),
       (3, 'Volkswagen');
insert into garages (id, name)
values (1, 'Garage1'),
       (2, 'Garage2'),
       (3, 'Garage3'),
       (4, 'Garage4');
insert into cars_location (car_id, garage_id)
values (1, 1),
       (1, 2),
       (1, 3),
       (2, 1),
       (2, 2),
       (2, 3),
       (2, 4),
       (3, 1),
       (3, 2),
       (3, 4);
-- Find all cars placed in all requested garages at the same time.
-- Example below:
/*
---------------------------------------------------------
cars       |   garages
---------------------------------------------------------
BMW        | Garage1, Garage2, Garage3                 - RETURNED!!
Audi       | Garage1, Garage2, Garage3, Garage4        - RETURNED!!
Volkswagen | Garage1, Garage2,                 Garage4 - NOT returned
---------------------------------------------------------
* /
select cars.name as cars,
       group_concat(
           garages.name
           ) as places
from cars_location
         left join cars on cars.id = cars_location.car_id
         left join garages on garages.id = cars_location.garage_id
-- where places in (1,2,3) # problem here because Volkswagen doesnt exist in garage 3 so should not be visible
group by 1

找到同时放置在所有请求的车库中的所有汽车。所以我想实现这个目标

---------------------------------------------------------
cars       |   garages
---------------------------------------------------------
BMW        | Garage1, Garage2, Garage3                 - RETURNED!!
Audi       | Garage1, Garage2, Garage3, Garage4        - RETURNED!!
Volkswagen | Garage1, Garage2,                 Garage4 - NOT returned because garage3 not present
---------------------------------------------------------

我不知道如何只构建一个sql查询

select cars.name as cars,
       group_concat(
           garages.name
           ) as places
from cars_location
         left join cars on cars.id = cars_location.car_id
         left join garages on garages.id = cars_location.garage_id
-- where places MUST BE in (1,2,3) # problem here because Volkswagen doesnt exist in garage 3 so should not be returned by sql
group by 1

http://sqlfiddle.com/#!9/eadd7a/1号机组