mySQL query with Woocommerce – Ambiguous Column

2 Views

I am trying to run an SQL query on my Woocommerce shop that outputs all customers names (first and last) who have purchased a certain set of items and the quantity ordered. The schema is standard WordPress/Woocommerce. My first query (below) worked, but doesn’t output quantity ordered: 

SELECT  `meta_value`,  `post_id`, `order_item_name` 
FROM  `wp_postmeta` AS pm  
JOIN wp_woocommerce_order_items AS oi ON pm.post_id = oi.order_id 
WHERE  `order_item_name` LIKE  'Photo%' AND  `meta_key` LIKE  '%shipping%name'

When trying to add the quantity to the query, I keep getting ambiguous column errors Column 'meta_value' in field list is ambiguous. When trying to add an alias to the table/column in various combinations, I get unknown column errors Unknown column 'wp_postmeta.meta_value' in 'field list'. This is my updated query:

SELECT  `meta_value` ,  `post_id` ,  `order_item_name`, `om.meta_key`
FROM  `wp_postmeta` AS pm
JOIN wp_woocommerce_order_items AS oi ON pm.post_id = oi.order_id
JOIN wp_woocommerce_order_itemmeta as om ON pm.order_item_id=om.order_item_id
WHERE `order_item_name` LIKE  'Photo%'
AND `pm.meta_key` LIKE  '%shipping%name'
AND `om.meta_key` LIKE '_qty'
Post Views: 2

Related posts

1 comment

  1. When using an alias, pm in your case, you can not also use the table name wp_postmeta

    Use 

    SELECT  `pm`.`meta_value`,  `post_id`, `order_item_name` 
FROM  `wp_postmeta` AS pm  
JOIN wp_woocommerce_order_items AS oi ON pm.post_id = oi.order_id 
WHERE  `order_item_name` LIKE  'Photo%' AND  `meta_key` LIKE  '%shipping%name'

    to get the desired result instead.

Comments are closed.