Menahid Menahid - 18 days ago 14
MySQL Question

mysql SELECT many to many GROUP_CONCAT (how to get all records)

this is my first question and sorry if my title is not very good and descriptive.

So i have this mysql query on many to many relationship and it is working fine, i get drivers and its units in format that i want.

select
d.*,
group_concat(DISTINCT u.make,'-', u.model)
from
drivers d
left join drivers_units dc
on d.driver_id = dc.driverId
left join units u
on dc.unitId = u.unit_id


My problem here is i don't get ALL drivers i get only drivers that have at least one unit i understand why (because that driver don't exist in driver_units table), my question here is:

What is best way to get all drivers (with and without any units), can i do it in one query (return all drivers no mater if they have units or not) or i must create new query to select drivers without units ?

I would appreciate mysql query example.

Answer

Something is wrong with your query, because you have an aggregation function (group_concat()) but no group by. Your query should be returning only one row.

To get all drivers, use the proper aggregation:

select d.*, 
       group_concat(distinct u.make,'-', u.model) 
from drivers d left join
     drivers_units dc 
     on d.driver_id = dc.driverId left join
     units u 
     on dc.unitId = u.unit_id
group by d.driver_id;

I am guessing that the distinct is unnecessary.