CODEWITHSUNDEEP
mysqlwordpresswoocommerce
Ered
Ered

Reputation: 497

SQL condition if doesn't exist

so I have this SQL query:

SELECT 
    p.ID
FROM 
    `cdlr_posts` p, 
    cdlr_postmeta pm 
WHERE 
    pm.post_id=p.ID AND 
    `post_type` = 'shop_order' AND
    pm.meta_key = '_statusCDLR' AND
    pm.meta_value <> 1 
group by
    p.ID

What I need is to show all the IDS if they match with those conditions, but I will also like to show the ones that do not contain the "_statusCDLR" meta_key I tried something like this with no luck: 

WHERE 
    pm.post_id=p.ID AND 
    `post_type` = 'shop_order' AND
    (pm.meta_key = '_statusCDLR' AND pm.meta_value <> 1 OR pm.meta_key <> '_statusCDLR') 
group by

Any help will be appreciated to achieve what I need.

Upvotes: 0

Views: 70

Answers (1)

GMB
GMB

Reputation: 222432

I understand that your requirement is to select the id of posts that :

  • either have a corresponding record in cdlr_post_meta with meta_key = 'statusCDLR' and meta_value <> 1

  • or do not have a record cdlr_post_meta with meta_key = 'statusCDLR'

A strategy to achieve this is to use a LEFT JOIN to search for a record in cdlr_post_meta with meta_key = 'statusCDLR', and then implement the rest of the logic in the WHERE clause (if there is no corresponding record, the columns of pm are all NULL).

SELECT p.ID
FROM cdlr_posts p
LEFT JOIN cdlr_postmeta pm 
    ON pm.post_id = p.ID AND pm.meta_key = '_statusCDLR'
WHERE 
    p.post_type = 'shop_order'
    AND ( pm.post_id IS NULL OR pm.meta_value <> 1 )
GROUP BY p.ID

PS - General remarks regarding your sql :

  • When mixing ORs and ANDs, you need to surround the test expressions within parentheses to avoid running into prescedence issues (AND has higher precedence than OR).

  • you should use explicit JOINs instead of implicit ones.

Upvotes: 1

Related Questions