CODEWITHSUNDEEP
mysqlsql
updater
updater

Reputation: 997

SQL group_by and group_concat if all values present in group

/*
 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

Find all cars placed in all requested garages at the same time. So I want to achieve this

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

I dont know how to build only one sql query

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

Upvotes: 1

Views: 56

Answers (2)

dnoeth
dnoeth

Reputation: 60472

Your problem is called Relational Division. Assuming cars_location (car_id, garage_id) is unique:

select cars.name as cars,
       group_concat(
           garages.name
           ) as places
from cars_location  -- no Outer Joins needed as you don't want non-matching locations
         join cars on cars.id = cars_location.car_id
         join garages on garages.id = cars_location.garage_id
where garages.id in (1,2,3) 
group by 1
having count(*) = 3 -- exactly three rows

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You want a having clause:

having sum(garage.id = 1) > 0 and
       sum(garage.id = 2) > 0 and
       sum(garage.id = 3) > 0

Upvotes: 1

Related Questions