CODEWITHSUNDEEP
sql
Rahul Mehta
Rahul Mehta

Reputation: 119

Can someone figure this subquery problem for me?

I need to get the expected output but we have to use sub-queries compulsory. I got the expected output without sub-queries but I am just not able to figure out how to get it with sub-queries. I have attached the ER diagram and the expected output.

Question Description: List full details of all types of RD account transactions made by all accounts of customers who are from 'Tamilnadu'. (Use subquery)

Query for expected output without using sub-queries:

select bt.*
from bank_transaction bt,bank_rd_account bra, bank_customer bc, bank_cust_contact bcc
where bt.trans_acc_no=bra.account_no
and bra.rd_cust_id=bc.cust_id
and bc.cust_id=bcc.cust_id
and cust_state='Tamilnadu'
order by trans_dt;

Expected Output ER diagram

Upvotes: 1

Views: 320

Answers (3)

Shraddha Vijay Bhagwat
Shraddha Vijay Bhagwat

Reputation: 1

SELECT * FROM bank_transaction t 
WHERE t.trans_acc_no IN(SELECT r.account_no 
        FROM bank_rd_account r, bank_customer c, bank_cust_contact cc 
        WHERE r.rd_cust_id = c.cust_id 
        AND c.cust_id = cc.cust_id 
        AND cc.cust_state = 'Tamilnadu') 
ORDER BY trans_no;

Upvotes: 0

Aarushi Bhansali
Aarushi Bhansali

Reputation: 11

Try the following code:

SELECT * FROM BANK_TRANSACTION bt  
WHERE bt.TRANS_ACC_NO IN  
(  
  SELECT bra.ACCOUNT_NO  
  FROM BANK_RD_ACCOUNT bra, BANK_CUSTOMER bc  
  WHERE bra.RD_CUST_ID = bc.CUST_ID  
  AND bc.CUST_ID = (SELECT bcc.CUST_ID FROM BANK_CUST_CONTACT bcc    
                    WHERE bcc.CUST_ID = bc.CUST_ID     
                    AND CUST_STATE='Tamilnadu')  
);

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16918

If I get your requirement correct, this following logic should help you getting your desired output.

Using appropriate joining is the best option for this purpose. But as you required solve this issue using sub query, you can consider the following sample code. 

SELECT *
FROM bank_transaction bt
WHERE bt.trans_acc_no IN
(
    SELECT bra.account_no
    FROM bank_rd_account bra
    INNER JOIN bank_customer bc 
        ON bra.rd_cust_id=bc.cust_id
    INNER JOIN bank_cust_contact bcc 
        ON bc.cust_id=bcc.cust_id
        and cust_state='Tamilnadu'
)

Upvotes: 2

Related Questions