Question 113644 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – how to sovle ambiguous column name: nation.n_nationkey in sql?

how do I solve the problem "ambiguous column name: n_name" and separate supplier and customer’s nation?

 SELECT count(l_orderkey)
FROM lineitem
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
INNER JOIN customer ON orders.o_custkey = customer.c_custkey
INNER JOIN nation ON customer.c_nationkey = nation.n_nationkey
INNER JOIN supplier ON lineitem.l_suppkey = supplier.s_suppkey 
INNER JOIN nation ON supplier.s_nationkey = nation.n_nationkey
INNER JOIN region ON nation.n_regionkey = region.r_regionkey
WHERE n_name = "UNITED STATES" AND r_name = "AFRICA"
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    SELECT COUNT(l_orderkey)
FROM lineitem
INNER JOIN supplier ON lineitem.l_suppkey = supplier.s_suppkey
INNER JOIN nation ON supplier.s_nationkey = nation.n_nationkey
INNER JOIN nation nation2 ON supplier.s_nationkey = nation2.n_nationkey
INNER JOIN region region2 ON nation2.n_regionkey = region2.r_regionkey
INNER JOIN region ON nation.n_regionkey = region.r_regionkey
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
INNER JOIN customer ON orders.o_custkey = customer.c_custkey
INNER JOIN nation nation1 ON customer.c_nationkey = nation1.n_nationkey
WHERE nation1.n_name = 'UNITED STATES' AND region2.r_name = 'AFRICA';

  2. 0

    you can split the query in two parts :

    • One for Customer count
    • And the seconde one for Supplier count

    And after that make a Sum of the count’s to get the final count result for the line order key as bellow :

    select Sum(Result.counts) from (
SELECT count(l_orderkey)  AS counts 
FROM lineitem
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
INNER JOIN customer ON orders.o_custkey = customer.c_custkey
INNER JOIN nation ON customer.c_nationkey = nation.n_nationkey
WHERE customer.c_nationkey = "UNITED STATES" 
union all
SELECT count(l_orderkey) AS counts 
FROM lineitem
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey
INNER JOIN supplier ON lineitem.l_suppkey = supplier.s_suppkey
INNER JOIN nation ON supplier.s_nationkey = nation.n_nationkey
WHERE supplier.s_nationkey = "AFRICA"
)Result;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search