troubleshooting Question

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

Avatar of Mike Tew
Mike TewFlag for Canada asked on
MySQL Server* MariaDB* joins* SQL join
3 Comments1 Solution24 ViewsLast Modified:
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, 
   ROW_NUMBER() 
   OVER( 
      PARTITION BY post_id 
      ORDER BY CASE 
         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

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+

ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros