Issue

This Content is from Stack Overflow. Question asked by oldsix

How to solve so customer and supplier can have their nationkey?

``````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
WHERE customer.c_nationkey = "UNITED STATES" AND supplier.s_nationkey = "AFRICA"
``````

Solution

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;
``````

``` This Question was asked in  StackOverflow by  oldsix and Answered by Hassan_B It is licensed under the terms of
CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.```