Ambiguous column error. Kindly resolve


#1

I am facing this error while joining the below pasted data sets to determine the number of customers which have not placed in order. Can you suggest the way to get it corrected?

order_customer_id|customer_fname|customer_lname|
±----------------±-------------±-------------+
| 1| Richard| Hernandez|
| 2| Mary| Barrett|
| 3| Ann| Smith|
| 4| Mary| Jones|
| 5| Robert| Hudson|
| 6| Mary| Smith|
| 7| Melissa| Wilcox|
| 8| Megan| Smith|
| 9| Mary| Perez|
| 10| Melissa| Smith|

|order_customer_id|
±----------------+
| 11599|
| 256|
| 12111|
| 8827|
| 11318|
| 7130|
| 4530|
| 2911|
| 5657|
| 5648|
| 918|
| 1837|
| 9149|
| 9842|
| 2568|
| 7276|
| 2667|
| 1205|
| 9488|
| 9198

sqlContext.
sql("select customer_lname, customer_fname " +
“from customers left outer join orders on customer_id=order_customers where order_customer_id is null” +
“order by customer_lname, customer_fname”)

ERROR:
org.apache.spark.sql.AnalysisException: Reference ‘order_customer_id’ is ambiguous, could be: order_customer_id#43, order_customer_id#39.; line 1 pos 103


Sign up for our state of the art Big Data cluster for hands on practice as developer. Cluster have Hadoop, Spark, Hive, Sqoop, Kafka and more.



#2

Modify query to use aliases for table names. Here is an example:

val opDF = sqlContext.sql("select oi.order_item_order_id, oi.order_item_product_id, p.product_name, p.product_price, oi.order_item_subtotal " +
"from order_items oi, products p " +
“where oi.order_item_product_id = p.product_id”)