Avatar of Mike Tew
Mike Tew
Flag for Canada

asked on 

How to I get left join to work correctly to show NULL.

I have 4 price points within row number that order depending each meta_key (ie. pricing_usd_promo to pricing_cad). I need to see if the product is in category id 75 (ie. dd.term_id = '75'. I went with a left join as I wan't it to be NULL if the product is not in dd.term_id = '75'.

The results currently show that all products do exist in '75' in each row. However, I know that this is not the case as I have verified it against a.ID. This leads me to believe the LEFT JOIN syntax is wrong, and it should be a separate select statement using NOT EXISTS. Below is the current query I have.

WITH pm AS ( SELECT post_id,LENGTH(FORMAT(REPLACE(meta_value, ',', ''), 2)) 
AS pricelength, FORMAT(REPLACE(meta_value, ',', ''), 2) AS price, 
      PARTITION BY post_id 
         WHEN meta_key = 'pricing_usd_promo' THEN 1 
         WHEN meta_key = 'pricing_usd' THEN 2 
         WHEN meta_key = 'pricing_cad_promo' THEN 3 
         ELSE 4 END ) AS rownumber 
FROM wp__postmeta 
WHERE meta_key IN ('pricing_usd_promo','pricing_usd','pricing_cad_promo' ,'pricing_cad') AND LENGTH(meta_value) > 0 
 SELECT COALESCE(pm.pricelength, 'Z') AS pricelength, 
 COALESCE(pm.price, '0') AS price, a.* 
 FROM wp__posts a 
 INNER JOIN wp__term_relationships e ON a.ID = e.object_id 
 INNER JOIN wp__term_taxonomy c ON c.term_taxonomy_id = e.term_taxonomy_id 
 INNER JOIN wp__terms d ON c.term_id = d.term_id 
 INNER JOIN pm ON a.ID = pm.post_id AND pm.rownumber = 1
 LEFT JOIN wp__term_relationships ee ON a.ID = ee.object_id 
 LEFT JOIN wp__term_taxonomy cc ON cc.term_taxonomy_id = cc.term_taxonomy_id 
 LEFT JOIN wp__terms dd ON cc.term_id = dd.term_id 
 WHERE e.object_id = a.ID 
 AND d.slug = 'rectangle-picnic-tables' 
 AND a.post_status='publish' 
 AND a.post_type = 'picnictables' 
 AND dd.term_id = '75' AND 
 EXISTS(SELECT * FROM wp__postmeta t WHERE t.post_id = a.ID) 
 GROUP BY a.id 
 Order By pricelength asc, price asc, a.post_title asc 
 LIMIT 28 offset 0

Open in new window

Also, I need to add a check to do something like the following:

If the product does not exist in dd.term_id = '75', then
if  'pricing_usd_promo' and 'pricing_usd' corresponding meta_value is empty, then
price = price/.82

If the product does exist in dd.term_id = '75', then
if  'pricing_usd_promo' and 'pricing_usd' corresponding meta_value is empty, then
price = price*.82

The server is on Maria DB 10+

* joins* MariaDBMySQL Server* SQL join

Avatar of undefined
Last Comment
Mike Tew

8/22/2022 - Mon