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?

Answer 1

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  

Answer 2

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