SQL LEFT JOIN Subquery Alias

Question

I'm running this SQL query:

SELECT wp_woocommerce_order_items.order_id As No_Commande
FROM  wp_woocommerce_order_items
LEFT JOIN 
    (
        SELECT meta_value As Prenom
        FROM wp_postmeta
        WHERE meta_key = '_shipping_first_name'
    ) AS a
ON wp_woocommerce_order_items.order_id = a.post_id
WHERE  wp_woocommerce_order_items.order_id =2198

And I get this error:

#1054 - Unknown column 'a.post_id' in 'on clause'.

I think my code is pretty simple, but I can't make it right. What am I doing wrong?

score 211 · Accepted Answer · answered May 27 '13 at 15:22

You didn't select post_id in the subquery. You have to select it in the subquery like this:

SELECT wp_woocommerce_order_items.order_id As No_Commande
FROM  wp_woocommerce_order_items
LEFT JOIN 
    (
        SELECT meta_value As Prenom, post_id  -- <----- this
        FROM wp_postmeta
        WHERE meta_key = '_shipping_first_name'
    ) AS a
ON wp_woocommerce_order_items.order_id = a.post_id
WHERE  wp_woocommerce_order_items.order_id =2198

EJay · Answer 2 · 2014-01-29T20:52:42.367

26

I recognize that the answer works and has been accepted but there is a much cleaner way to write that query. Tested on mysql and postgres.

SELECT wpoi.order_id As No_Commande
FROM  wp_woocommerce_order_items AS wpoi
LEFT JOIN wp_postmeta AS wpp ON wpoi.order_id = wpp.post_id 
                            AND wpp.meta_key = '_shipping_first_name'
WHERE  wpoi.order_id =2198

edited Jan 29 '14 at 20:52

answered Jan 29 '14 at 20:47

EJay

1,079
2
12
23

1

Hi, Out of interest, would this method be more efficient? Alternatively would it be more efficient to move the "AND wpp.meta_key = '_shipping_first_name'" to the WHERE clause? – Chris Feb 19 '14 at 14:51
1

Honestly, I have no idea on either of those. I just came across this answer and noticed that there was cleaner way to write the query so I figured I would point that out. Sorry I can't be of more help. – EJay Feb 24 '14 at 17:42
1

In MySQL/PostgreSQL you can use `EXPLAIN SELECT ...` or for MSSQL `SET SHOWPLAN_ALL ON` or `SET SHOWPLAN_XML ON` to see how rows are retrieved. In MySQL `used filesort`, `used temporary` are slow and should be avoided. As for the joined subquery, it requires retrieving all rows matching the meta_key value from the wp_postmeta table before joining on post/order id's. So it should be safe to assume that it would be faster to match on the order/post id's and meta_key. Generally a subquery like the one you used is best with an `ORDER BY` `LIMIT` that can't be filtered from the main query. – Will B. Jan 19 '15 at 21:59
2

Examples from SQLFiddle results: `Criteria on Join` http://sqlfiddle.com/#!2/e84fa/5 and `Subquery on Join` http://sqlfiddle.com/#!2/e84fa/3 Note that the `Subquery on Join` retrieved 22 rows from wp_postmeta while the `Criteria on Join` only retrieved 1 from wp_postmeta. – Will B. Jan 19 '15 at 22:49

SQL LEFT JOIN Subquery Alias

2 Answers2

Linked