Click here to Skip to main content
15,881,380 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
customer table (CUST_ID,CUST_NAME,CUST_ADDRESS,EMP_ID)

has table (CUST_ID,ACCOUNT_NUMBER)

take table (CUST_ID,LOAN_NUMBER)

table loan (LOAN_NUMBER,AMOUNT,BRANCH_NAME)

table account(ACCOUNT_NUMBER,ACCOUNT_BALANCE)


Q1 Find all customer id for customers who have an account but no loan

What I have tried:

select distinct c.cust_id as customer_taking_loan
from customer c
join has h on c.cust_id=h.cust_id
join take t on c.cust_id=t.cust_id
where c.cust_id=h.cust_id
(
where not exist
(select 1 from take t c.cust_id=t.cust_id))
order by c.cust_id
Posted
Updated 4-Mar-23 16:13pm

1 solution

You're not too far off with what you got; try this out
SQL
SELECT Distinct c.Cust_ID
FROM   Customer c
INNER JOIN Has  h ON c.Cust_ID = h.Cust_ID
WHERE  Cust_ID NOT IN (SELECT Cust_ID FROM Take)
You may be able to get away with this one as well
SQL
SELECT Distinct Cust_ID
FROM   Has
WHERE  Cust_ID NOT IN (SELECT Cust_ID FROM Take)
 
Share this answer
 
Comments
Abed Al Rahman Hussien Balhawan 1-Mar-20 22:29pm    
the first method was missing a C. before the cust_id

thank you for showing me two methods MadMyche i wish you all the best in your career.

can you explain to me where i was wrong and why?
MadMyche 2-Mar-20 7:08am    
You are correct; the WHERE clause in the first code sample would throw an error about CustID is ambiguous, good job on figuring that out on your own.

To know where/why you went wrong would require me to know what your thought process was when you wrote the query- I can tell you what my thought process was when I wrote the queries and why they work:

To get all the unique customers who had accounts we do an INNER JOIN between the customer and has tables.
As this is, it will also contain the customers who have loans, so we remove those via a subquery of the take table

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900