| Which SQL statement is correct? [message #1678] |
Tue, 21 May 2002 23:50  |
karunamoorthy_p
Messages: 13
Registered: April 2002
|
Junior Member |
|
|
Question:
Find all customers who have an Account and loan at CHENNAI branch.
Answer 1:
select customer_name from account_holders
where customer_name in (
select customer_name from loan
where branch_name = 'CHENNAI');
Answer 2:
select customer_name from account_holders
where branch_name = 'CHENNAI'
and customer_name in (
select customer_name from loan);
Plese tell me which answer is correct.
|
|
|
|
| Re: Which SQL statement is correct? [message #1685 is a reply to message #1678] |
Wed, 22 May 2002 06:41  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Neither,
Answer 1 will return all customers who have an account at ANY branch and a loan at the CHENNAI branch.
Answer 2 will return all customers who have an account at the CHENNAI branch and a loan at ANY branch.
Either of these will work:
select customer_name from account_holders
where branch_name = 'CHENNAI'
and customer_name in (select customer_name
from loan
where branch_name = 'CHENNAI');
select customer_name
from account_holders
where branch_name = 'CHENNAI'
and customer_name in (select customer_name
from loan
where branch_name = 'CHENNAI');
but this may be the simplest way:
select a.customer_name
from account_holders a,
loan l
where a.branch_name = 'CHENNAI'
and l.branch_name = a.branch_name
and l.customer_name = a.customer.name;
this could be extended to show all customers having both an account and a loan at the same branch for all branches.
select a.branch_name, l.customer_name
from account_holders a,
loan l
where l.branch_name = a.branch_name
and a.customer_name = l.customer.name
order by a.branch_name;
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
