select all id's where values are different-CodePudding

I have a woocommerce DB Products are stored in rows where each row has: post_id (int not unique) meta_key (varchar) meta_value (double)

I am trying to find all post_id where the value of meta_key which equals to "_price" is different from the value of meta_key which equals to "_sale_price".

This is what I tried which of course didnt work

SELECT  * FROM `yai_postmeta`
where meta_key = "_price" <> meta_key = "_sale_price"

CodePudding user response：

Maybe this is what you mean?

show records where the _sale_price is different than the _price of the same post_ID. I assume the absence of a corresponding record is different. or if both values are null that's "different"

SELECT coalesce(A.post_ID, B.Post_ID) as Post_ID, A.meta_value,  B.Meta_value
FROM `yai_postmeta` A
FULL OUTER JOIN `yai_postmeta` B
  on A.post_ID = B.post_ID 
 and A.meta_key = '_price
 and B.meta_key = '_sale_price'
WHERE A.meta_value <> b.Meta_value 
   OR A.meta_value is null 
   OR B.meta_value is null

CodePudding user response：

If you only want the post_id values then this might be what you are looking for:

select distinct yp.post_id
  from yai_postmeta as yp
  where exists (
    select 42
      -- Take pairs of rows from the table.
      from yai_postmeta as Lyp inner join
        yai_postmeta as Ryp on
          -- The two rows refer to the same   post_id   as the outer query.
          Lyp.post_id = yp.post_id and Ryp = yp.post_id and
          -- One row's key is   '_price'  and the other's is   '_sale_price' .
          Lyp.meta_key = '_price' and Ryp.meta_key = '_sale_price' and
          -- The values for the two keys aren't equal.
          Lyp.meta_value <> Ryp.meta_value );