I need to create a view in sql that I will use on my queries.
I am using the following tables:
customer (name:string,credit:integer)
loan (no:string,type:string,minCredit:integer)
borrower (cname:string,lno:string,due:date)
where borrower.cname and borrower.lno are foreign keys referencing customer, respectively loan, whose keys are name , respectively no (number). Attribute loan. minCredit indicates the minimum credit required of a customer to qualify for that loan.
I am trying to create a view that shows customers and loans they havent taken.
Create view LoansNotTaken AS
SELECT c.name, l.no
FROM customer c, loan l
WHERE NOT EXISTS(
SELECT c1.name,l1.no
FROM customer c1, loan l1, borrower b1
WHERE c1.name=b1.cname AND l1.no=b1.lno AND lno=l1.no AND c.name=c1.name)
My basic idea in the second select is get all the customer and loans they have taken pairs. Then im trying to use the not exists to give me all pairs excluding those. However my result only gives me pairs as long as the customer didnt take out any loans whatsoever, and I need the one they didnt take out even if they did take out some. Can anyone plz help me to understand what Im doing wrong in my query.
Aucun commentaire:
Enregistrer un commentaire