CODEWITHSUNDEEP
sqloracle-database
Samit Paudel
Samit Paudel

Reputation: 181

How to join tables to subquery results in SQL?

I have following query:

SELECT customer_id, product_id, COUNT(product_id) CUSTOMER_PRODUCT_COUNT
  2  FROM Order_Details
  3  Group BY customer_id, product_id
  4  HAVING COUNT(product_id) > 1;

which results in following table:

CUSTOMER_ID PRODUCT_ID CUSTOMER_PRODUCT_COUNT
---------- ---------- ----------------------
C1         P3                              2

I am trying to use this as a subquery to join the customer_ID to customer_name from Customer table. The product_id column is also present in another table named Order_Details which I am also trying to join so that it gives address of the customer:

So far I've come up with following query which gives an error:

SELECT c.customer_name, a.country, a.zone, a.district, a.city
  2  FROM (
  3     SELECT customer_id, product_id, COUNT(product_id) CUSTOMER_PRODUCT_COUNT
  4     FROM Order_Details
  5     Group BY customer_id, product_id
  6     HAVING COUNT(product_id) > 1) AS o
  7  JOIN Customer c ON c.customer_id = o.customer_id
  8  JOIN Address a ON a.address_id = o.address_id;

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This query looks correct:

SELECT c.customer_name, a.country, a.zone, a.district, a.city
FROM (SELECT customer_id, product_id, COUNT(product_id) CUSTOMER_PRODUCT_COUNT
      FROM Order_Details
      GROUP BY customer_id, product_id
      HAVING COUNT(product_id) > 1
     ) AS o JOIN
     Customer c
     ON c.customer_id = o.customer_id JOIN
     Address a
     ON a.address_id = o.address_id;

Apart from the reference to o.address_id which I would guess is intended as c.address_id, I can only think of two possible problems:

  • The column references are not correct.
  • You are using Oracle, which doesn't allow as in the FROM clause.

In any case, I would write the query like this:

SELECT c.customer_name, a.country, a.zone, a.district, a.city
FROM (SELECT customer_id, product_id, COUNT(*) as CUSTOMER_PRODUCT_COUNT
      FROM Order_Details od
      GROUP BY customer_id, product_id
      HAVING COUNT(*) > 1
     ) o JOIN
     Customer c
     ON c.customer_id = o.customer_id JOIN
     Address a
     ON a.address_id = c.address_id;

There is no need to count the non-NULLvalues in a column when you simply want to count rows. That is whatCOUNT(*)` does.

Upvotes: 1

Related Questions