CODEWITHSUNDEEP
sqlinner-join
In6ify
In6ify

Reputation: 147

Query table using inner join?

We are trying to find the names of the customers who have both a loan and an account in the same branch. Should we use inner join here? So far we have only written;

select DISTINCT customer.name
FROM Customer, Has_Loan, Branch, Has_Account 
WHERE

enter image description here

We have tried a few different things without getting any further, so appreciate any kind of help or hint:)

Upvotes: 0

Views: 86

Answers (2)

RLOG
RLOG

Reputation: 630

select c.name
from customer c
join has_loan hl on hl.lssn = c.ssn
join loan l on hl.lno = l.loanno
join has_account ha on ha.assn = c.ssn
join account a on ha.ano = a.accountno
join branch b on b.branchid = a.branchid
where l.branchid  = a.branchid
group by c.name;

Upvotes: 1

Nenos
Nenos

Reputation: 129

You can try this :

SELECT DISTINCT Customer.Name
FROM Customer
    INNER JOIN Has_Account ON Customer.Ssn = Has_Account.Assn
    INNER JOIN Account ON Account.AccountNo = Has_Account.ANo
    INNER JOIN Has_Loan ON Customer.Ssn = Has_Loan.Lssn
    INNER JOIN Loan ON Loan.LoanNo = Has_Loan.LNo
WHERE Loan.BranchID = Account.BranchID

Upvotes: 1

Related Questions