CODEWITHSUNDEEP
mysqlsql
Steveo
Steveo

Reputation: 147

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_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

I want the results to be combined with this statement:

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

How to combine the 2? I have tried UNION, also tried similar statement to below but phpMyAdmin crashes out.

SELECT x.a, y.b FROM (SELECT * from a) as x, (SELECT * FROM b) as y

I am looking for output with 2 columns:

1 x product_id

1 x variation_id

Upvotes: 1

Views: 62

Answers (4)

Shuwn Yuan Tee
Shuwn Yuan Tee

Reputation: 5748

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;

Upvotes: 0

Eric Brandt
Eric Brandt

Reputation: 8101

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(CASE WHEN meta_key = '_product_id' THEN meta_value ELSE '' END) AS product_id,
      MAX(CASE WHEN meta_key = '_variation_id' THEN meta_value ELSE '' END) AS variation_id
    FROM
      ctc_woocommerce_order_itemmeta
  ) AS im
INNER JOIN 
  ctc_woocommerce_order_items AS oi 
    ON im.order_item_id = oi.order_item_id
    AND
    oi.order_id = 3988

Upvotes: 0

Adam Lesiak
Adam Lesiak

Reputation: 501

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 = 3988
AND sub1.meta_key = '_variation_id' AND sub1.order_id = 3988

Upvotes: 0

Heichou
Heichou

Reputation: 311

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;

Upvotes: 1

Related Questions