Get values from SQL LEFT JOIN Subquery-CodePudding

I need to get values from a subquery inside a LEFT JOIN.

I will use here the example used in this answer: https://stackoverflow.com/a/16776190/17580361

SELECT wp_woocommerce_order_items.order_id As No_Commande
FROM  wp_woocommerce_order_items
LEFT JOIN 
    (
        SELECT meta_value As Prenom, post_id
        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

Now how can I select values from wp_postmeta table?

If you need more info, please tell me.

Thank you very much.

CodePudding user response：

Your subquery is unnecessary. Join the other table directly.

SELECT
    oi.order_id As No_Commande,
    m.meta_value As Prenom
FROM 
    wp_woocommerce_order_items oi
    LEFT JOIN wp_postmeta m ON m.post_id = oi.order_id AND m.meta_key = '_shipping_first_name'
WHERE
    oi.order_id = 2198

That being said, nothing keeps you from including columns from a joined subquery in the main query. Joined subqueries behave exactly like tables.

CodePudding user response：

SELECT 
    wp_woocommerce_order_items.order_id As No_Commande,
    a.Prenom
FROM  wp_woocommerce_order_items
LEFT JOIN 
    (
        SELECT meta_value As Prenom, post_id
        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