Outer left joining two lookup tables

Databases
1

I have two lookup tables
One for pickup locations
One for extras

The problem is I’m getting each locations entry in double
and each extras entry in 7x.

I know my syntax is wrong but I’m not sure how to code what I need.

This is the syntax I’m using to link get all the info


SELECT products.id
	, products.title
     	, products.cost
     	, products.extra_cost
     	, products.extra_cost_note
     	, products.saving_note
     	, products.picture_url
     	, GROUP_CONCAT(pickup_locations.location SEPARATOR "\
") AS locations
     	, GROUP_CONCAT(extras.title SEPARATOR "\
") AS extras
	, products.status
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
LEFT OUTER JOIN product_extras
	ON product_extras.product_id = products.id
LEFT OUTER JOIN extras
	ON extras.id = product_extras.extras_id
WHERE products.status = 1 || products.status = 2
GROUP BY products.id
ORDER BY products.title
2

A few more details.

a product can have multiple pickup locations and multiple extras. So the product_pickup and product_extras are basic lookup tables with two columns (product_id & pickup_id) or (product_id & extra_id). If I remove the two last left outer joins, I get all the right pickup locations but as soon as I re-add the two last left outer joins I get repeated values.

3

a classic example of cross join effects produced by have two one-to-many relationships in the same query

solved by the classic technique of pushing down the grouping into subqueries

SELECT products.id
     , products.title
     , products.cost
     , products.extra_cost
     , products.extra_cost_note
     , products.saving_note
     , products.picture_url
     , COALESCE(l.locations,'') AS locations
     , COALESCE(e.extras,'')    AS extras
     , products.status
  FROM products
LEFT OUTER 
  JOIN ( SELECT product_pickup.product_id
              , GROUP_CONCAT(pickup_locations.location SEPARATOR "\
") AS locations
           FROM product_pickup
         INNER 
           JOIN pickup_locations
             ON pickup_locations.id = product_pickup.pickup_id
         GROUP
             BY product_pickup.product_id ) AS l
    ON l.product_id = products.id
LEFT OUTER 
  JOIN ( SELECT product_extras.product_id
              , GROUP_CONCAT(extras.title SEPARATOR "\
") AS extras
           FROM product_extras
         INNER 
           JOIN extras
             ON extras.id = product_extras.extras_id
         GROUP
             BY product_extras.product_id ) AS e
    ON e.product_id = products.id
 WHERE products.status IN ( 1 , 2 )
ORDER 
    BY products.title

:slight_smile:

4

Rudy, you my friend, are the MAN!
That worked perfectly!

1- Using that technique, could I repeat with more then 2x 1-to-many relationships?
2- Why is coalesce necessary?

5

thanks for the kind words :slight_smile:

  1. yes, because each subquery takes care of producing a derived table that has (zero or) one row per product, so as long as you keep joining on a product by product basis, you can keep on doing that

  2. COALESCE simply ensures a value (even though it’s a zero-length string) instead of returning a NULL which some displays or apps have trouble handling – you could default it to a literal string that says something like ‘None’ or ‘No results’ as well