Combine 2 Select Left Join Queries, Union/declaring As Not Working

I have this select statement, it runs a single column with heading 'product_id' and a value. SELECT meta_value as product_id FROM ctc_woocommerce_order_itemmeta LEFT JOIN ctc_wooco

Solution 1:

I think you wrote the SELECTs in the wrong place.

SELECT (
  SELECT meta_value as product_id
  FROM ctc_woocommerce_order_itemmeta
  LEFT JOIN ctc_woocommerce_order_items ON ctc_woocommerce_order_itemmeta.order_item_id = ctc_woocommerce_order_items.order_item_id
  WHERE meta_key = '_product_id'AND order_id = 3988
) as product_id,
(
  SELECT meta_value as variation_id
  FROM ctc_woocommerce_order_itemmeta
  LEFT JOIN ctc_woocommerce_order_items ON ctc_woocommerce_order_itemmeta.order_item_id = ctc_woocommerce_order_items.order_item_id
  WHERE meta_key = '_variation_id'AND order_id = 3988
) as variation_id;

Solution 2:

Try JOIN with subquery:

SELECT t1.meta_value as product_id, sub1.variation_id AS variation_id
FROM ctc_woocommerce_order_itemmeta t1
LEFT JOIN ctc_woocommerce_order_items ON 
ctc_woocommerce_order_itemmeta.order_item_id = 
ctc_woocommerce_order_items.order_item_id

JOIN

    (SELECT meta_key, order_id, meta_value as variation_id
        FROM ctc_woocommerce_order_itemmeta
        LEFT JOIN ctc_woocommerce_order_items ON 
        ctc_woocommerce_order_itemmeta.order_item_id = 
        ctc_woocommerce_order_items.order_item_id
    ) AS sub1

WHERE t1.meta_key = '_product_id' AND t1.order_id = 3988AND sub1.meta_key = '_variation_id' AND sub1.order_id = 3988

Solution 3:

I'd try to sort out the two id fields in a subquery then join that to the order lines table.

Also, when you add a WHERE condition on a field in a table you're OUTER JOINing to, it becomes an INNER JOIN, which I think is really what you want, anyway. For the sake of readability, I added table aliases.

There might be a more elegant way to get the id fields out of that subquery, but without sample data I used a method I was pretty sure would work. A self join might be more performant, though.

SELECT
  im.product_id,
  im.variation_id
FROM
  (
    SELECT
      order_item_id,
      MAX(CASEWHEN meta_key ='_product_id'THEN meta_value ELSE''END) AS product_id,
      MAX(CASEWHEN meta_key ='_variation_id'THEN meta_value ELSE''END) AS variation_id
    FROM
      ctc_woocommerce_order_itemmeta
  ) AS im
INNERJOIN 
  ctc_woocommerce_order_items AS oi 
    ON im.order_item_id = oi.order_item_id
    AND
    oi.order_id =3988

Solution 4:

It looks to me you don't even need the LEFT JOIN with ctc_woocommerce_order_items table. Since no fields are required from ctc_woocommerce_order_items table. Also with table_A LEFT JOIN table_B, it means no matter matching record exists in table_B or not, always return record from table_A. So I simplify it here:

SELECT
    a.product_id,
    b.variation_id
FROM
(
    SELECT
        order_id,
        meta_value as product_id
    FROM
        ctc_woocommerce_order_itemmeta
    WHERE
        meta_key = '_product_id'AND order_id = 3988
    LIMIT 1
) a

JOIN

(
    SELECT
        order_id,
        meta_value as variation_id
    FROM
        ctc_woocommerce_order_itemmeta
    WHERE
        meta_key = '_variation_id'AND order_id = 3988
    LIMIT 1
) b

ON a.order_id = b.order_id;