CODEWITHSUNDEEP
mysqlsql
Modaresi
Modaresi

Reputation: 233

subquery to find the customers that have placed an order

I have just started learning MYSQL and while I am very good with joins, I am having lots of problems with subqueries. So I have decided to practice more. I want to find the customers and their id, who have placed an order in the past. The item's price must be $6.5 or higher. Using joins this is how I have done it:

SELECT 
CUSTOMER_TBL.CUST_ID
, CUST_NAME
FROM 
CUSTOMER_TBL
INNER JOIN ORDERS_TBL
ON ORDERS_TBL.CUST_ID = CUSTOMER_TBL.CUST_ID
INNER JOIN PRODUCTS_TBL 
ON ORDERS_TBL.PROD_ID = PRODUCTS_TBL.PROD_ID
WHERE PRODUCTS_TBL.COST > 6.5

Here is how I have done using subquery.

SELECT CUSTOMER_TBL.CUST_ID
, CUST_NAME
FROM CUSTOMER_TBL
WHERE 
EXISTS (
SELECT * 
FROM ORDERS_TBL
WHERE ORDERS_TBL.CUST_ID = CUSTOMER_TBL.CUST_ID
)

As you can see I got up to the part of including only those who have placed an order. But I cannot figure out the rest; of orders, only those that were higher than $6.5

Upvotes: 3

Views: 374

Answers (2)

Deepak Rai
Deepak Rai

Reputation: 2203

SELECT CUST_ID, CUST_NAME FROM CUSTOMER_TABLE
WHERE CUST_ID IN
(SELECT CUST_ID FROM ORDERS_TBL 
WHERE PROD_ID IN 
(SELECT PROD_ID FROM PRODUCTS_TBL
WHERE COST > 6.5))

This might help you.

Upvotes: 2

Arion
Arion

Reputation: 31239

Something like this maybe:

SELECT 
  CUSTOMER_TBL.CUST_ID,
  CUST_NAME
FROM 
  CUSTOMER_TBL
WHERE EXISTS 
(
   SELECT 
     * 
   FROM 
     ORDERS_TBL
     INNER JOIN PRODUCTS_TBL 
        ON ORDERS_TBL.PROD_ID = PRODUCTS_TBL.PROD_ID
     WHERE 
        ORDERS_TBL.CUST_ID = CUSTOMER_TBL.CUST_ID
        AND PRODUCTS_TBL.COST > 6.5
)

Upvotes: 1

Related Questions