Click here to Skip to main content
14,639,649 members
Rate this:
Please Sign up or sign in to vote.
See more:
customer table (CUST_ID,CUST_NAME,CUST_ADDRESS,EMP_ID)
take table (CUST_ID,LOAN_NUMBER)
table loan (LOAN_NUMBER,AMOUNT,BRANCH_NAME)

Find the customer name, loan number and loan amount of all customers having a loan
at the Perryridge branch.

What I have tried:

select c.cust_name, l.loan_number,l.amount
from customer c,loan l,take t
join take t on c.cust_id=t.cust_id
join loan l on l.loan_number=t.loan_number
where branch_name=’perryridge’;
Posted
Updated 2-Mar-20 4:54am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

This should give you an idea:
SELECT
  c.cust_name,
  l.loan_number,
  l.amount
FROM customer c
LEFT JOIN take t ON t.cust_id = c.cust_id
LEFT JOIN loan l ON l.loan_number = t.loan_number
WHERE l.branch_name = ’perryridge’ AND NOT l.loan_number IS NULL

Ok, it is just a guess according the information we have so far.
   
v3
Comments
   
i tried this one before seeing your idea and it worked , would u accept it as a solution aswell ?

select c.cust_name, t.loan_number,l.amount,l.branch_name
from customer c
join take t on c.cust_id=t.cust_id
join loan l on l.loan_number=t.loan_number
where l.branch_name='Perryridge'
0x01AA 2-Mar-20 11:12am
   
"would u accept it as a solution aswell ?"
The only one who can accept the solution is you ;)

In case you asking is your SQL ok: Now I would say it is important to check AND NOT l.loan_number IS NULL. This prevent you to check unimportant resuls in the code.

[Edit]
Thank you for accepting
   
thank you for the help and clarification wish you all the best in your career. :)
i would rate you 10 star vote if possible.
0x01AA 2-Mar-20 11:26am
   
You are very welcome. Thank you also and while I'm trying to help I'm still learning also ;) All the best for you also.
Maciej Los 2-Mar-20 11:40am
   
5ed!
0x01AA 2-Mar-20 11:48am
   
Thank you very much Maciej. As long as no CTE and no Pivot is involved, I'm feeling comfortable with SQL :)
Maciej Los 2-Mar-20 11:59am
   
First thought: practice makes master. ;)
You'll feel comfortable with those very soon.
0x01AA 2-Mar-20 12:59pm
   
I'm an old man so it will take time, very much time ;)
Maciej Los 2-Mar-20 13:11pm
   
:laugh:
The author of relativity theory probably would say: it depends...
:D
   
can you help me with this one ?

The salary of employee dealing with customer having id=4 has been doubled

i tried this:

update employee e
set e.emp_salary=e.emp_salary*2
from employee e
join customer c on c.emp_id=e.emp_id
having c.cust_id=4

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100