troubleshooting Question

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

Avatar of Mike Tew
Mike TewFlag for Canada asked on
* joins* MariaDBMySQL Server* SQL join
3 Comments1 Solution25 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

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+

ASKER CERTIFIED SOLUTION
ste5an
Senior Developer

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

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 3 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 3 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004