Fine tune a group_concat query

The problem with this query is it doesn’t list the products that don’t have pickup locations. Any ideas on how to change it around?

SELECT products.id, products.title, products.cost, products.extra_cost, products.extra_cost_note, products.saving_note, products.picture, 
GROUP_CONCAT(pickup_locations.location separator "\
")
FROM products, product_pickup, pickup_locations
WHERE product_pickup.product_id = products.id AND product_pickup.pickup_id = pickup_locations.id
GROUP BY products.id
ORDER BY products.title;

you’re using implicit inner joins, the old style, with commas in the FROM clause

you want outer joins instead, specifically LEFT OUTER JOINs


SELECT products.id
     , products.title
     , products.cost
     , products.extra_cost
     , products.extra_cost_note
     , products.saving_note
     , products.picture
     , GROUP_CONCAT(pickup_locations.location 
            SEPARATOR "\
") AS locations
  FROM products
LEFT OUTER
  JOIN product_pickup
    ON product_pickup.product_id = products.id
LEFT OUTER
  JOIN pickup_locations
    ON pickup_locations.id = product_pickup.pickup_id
GROUP 
    BY products.id
ORDER 
    BY products.title

[ot]

nope, buffalo bills, usually, although this year they are doomed, so i’m pulling for da detroit lions :D[/ot]

You my friend are a good man! Thanks so much! It’s too bad you’re probably a Leafs fan :slight_smile: