Mqsyl - multiple left joins

Question

I'm trying to improve a live product search for the admin side of our online store.

We're currently using the following query:

SELECT p.product_id, p.full_title, p.descript, p.cost, p.no_vat, p.high_pic, p.prod_type, count(p.product_id) AS occurence, t.descript AS type_desc, p.available
FROM gdd_product as p, gdd_prodtype as t, gdd_info as i 
LEFT JOIN gdd_keyword as k ON i.product_id = k.product 
WHERE p.prod_type = t.prod_type 
AND i.product_id = p.product_id 
AND replace(concat_ws(p.descript, i.info_search1, i.info_search2, i.info_search3, k.keyword),' ','') 
LIKE '%tool%' 
GROUP BY p.product_id 
ORDER BY occurence DESC, cost ASC LIMIT 30

This works, but omits any results which don't have an entry in the info_search columns.

So I tried changing it so that gdd_info is LEFT JOINed, with this code:

SELECT p.product_id, p.full_title, p.descript, p.cost, p.no_vat, p.high_pic, p.prod_type,
count(p.product_id) AS occurence, t.descript AS type_desc, p.available 
FROM gdd_product as p, gdd_prodtype AS t
LEFT JOIN gdd_info as i ON p.product_id = i.product_id 
LEFT JOIN gdd_keyword as k ON p.product_id = k.product 
WHERE p.prod_type = t.prod_type 
AND replace(CONCAT_WS(p.descript, i.info_search1, i.info_search2, i.info_search3, k.keyword),' ','')
LIKE '%tool%' 
GROUP BY p.product_id
ORDER BY occurence DESC, cost ASC
LIMIT 30

...but that throws an error: SQL Error (1054): Unknown column 'p.product_id' in 'on clause'

What am I doing wrong?

Answer 1

1st. Don't mix standards the ANSI standards (ANSI-92 vs ANSI-89). Either use INNER/CROSS/LEFT join or the , notation but not both. It's bad form and may eventually break; and maybe what's causing your p.product error now.

SELECT p.product_id, p.full_title, p.descript, p.cost
     , p.no_vat, p.high_pic, p.prod_type
     , count(p.product_id) AS occurence, t.descript AS type_desc
     , p.available 
FROM gdd_product as p
INNER JOIN  gdd_prodtype AS t
 on p.prod_type = t.prod_type 
LEFT JOIN gdd_info as i 
  ON p.product_id = i.product_id 
LEFT JOIN gdd_keyword as k 
  ON p.product_id = k.product 
WHERE replace(CONCAT_WS(p.descript, i.info_search1, i.info_search2, i.info_search3, k.keyword),' ','') 
LIKE '%tool%' 
GROUP BY p.product_id
ORDER BY occurence DESC, cost ASC
LIMIT 30

2nd. the reason why it's omitting records is likely because NULL concatenated with a string is NULL. Then you search for a string against a null which will never return a result. You need to coalesce the i.info_search1... with a empty set '' coalesce(i.info_earch1,'') and so on... so it takes the 1st non-null value in a series and then a string is compared against a string

replace(CONCAT_WS(
                   coalesce(p.descript,'')
                 , coalesce(i.info_search1,'')
                 , coalesce(i.info_search2,'')
                 , coalesce(i.info_search3,'')
                 , coalesce(k.keyword,'')),' ','')

Giving us...

   SELECT p.product_id, p.full_title, p.descript
        , p.cost, p.no_vat, p.high_pic, p.prod_type
        , count(p.product_id) AS occurence, t.descript AS type_desc
        , p.available 
    FROM gdd_product as p
    INNER JOIN  gdd_prodtype AS t
     on p.prod_type = t.prod_type 
    LEFT JOIN gdd_info as i 
      ON p.product_id = i.product_id 
    LEFT JOIN gdd_keyword as k 
      ON p.product_id = k.product 
    WHERE replace(CONCAT_WS(
                    coalesce(p.descript,'')
                  , coalesce(i.info_search1,'')
                  , coalesce(i.info_search2,'')
                  , coalesce(i.info_search3,'')
                  , coalesce(k.keyword,'')),' ','')
          LIKE '%tool%' 
    GROUP BY p.product_id
    ORDER BY occurence DESC, cost ASC
    LIMIT 30

Think of it this way...

Say p.descript exists but info doesn't on your left join... so you concat p.descript with null getting null. Nothing will be like null so you get no records as you can't execute an equality check (like) on a null value and expect to get a result.

Now say p.descript doesn't exist and is null, concat it with anything that is null is yet again null so you have the same result.

Since any value could be null in your concat_WS string we need to coalesce all values just in case.

and now we have a valid string compared against your like and thus when your string matches your like, you'll now get results instead of when a column value in your ws_concat being null wiping your record out.