Question 31092 in Woocommerce Question posted in
The official Woocommerce documentation can be found here.

select all id's where values are different – Woocommerce

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"
Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.post_id = 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 );

    Runnable, with extended sample data (dbfiddle):

    declare @yai_postmeta as Table
 ( post_id int, meta_key varchar(20), meta_value varchar(20) );

insert into @yai_postmeta ( post_id, meta_key, meta_value ) values

  -- Fails: Prices are equal.
  ( 7, '_price', 'USD13.95'),
  ( 7, '_height', '0.8 furlong'),
  ( 7, '_sale_price', 'USD13.95'),

  -- Passes: There is a masmatched price pair.
  ( 13, '_price', 'USD13.95'),
  ( 13, '_price', '10/6'),
  ( 13, '_sale_price', 'USD13.95'),

  -- Fails: One price is missing.
  ( -1, '_depth', '6 feet'),
  ( -1, '_area', '0.75 barn'),
  ( -1, '_sale_price', 'USD13.95'),

  -- Passes: Prices are different.
  ( 0, '_price', 'USD13.95'),
  ( 0, '_width', 'pinky'),
  ( 0, '_sale_price', 'arm & leg');

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.post_id = 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 );
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search