sql statement to join three tables

Question

I need help

I have three tables as below :

customers [ CID , name , address , phone ] 

contracts [ ID , CID , TotalPrice , ] 

Payment [ ID , CID , pay ]

I want to get the name of customer , his phone number and the value of sum( contracts .TotalPrice ) - sum(Payment.pay) as Remaning

but i have trouble to do this

I use microsoft access 2007

I use this statment

select customers.name , customers.phone , customers.address, (sum( contracts .TotalPrice ) - sum(Payment.pay)) as Remaning from customers  inner join  contracts on customers.CID=contracts.CID  inner join Payment on contracts.CID=Payment.CID ";

Praveen Prasannan · Accepted Answer · 2013-08-15 16:54:34Z

1

select customers.CID ,customers.[name] , customers.phone , customers.address, 
(sum( contracts .TotalPrice ) - sum(Payment.pay)) as Remaning 
from customers  inner join  contracts on customers.CID=contracts.CID  
inner join Payment on contracts.CID=Payment.CID GROUP BY customers.CID";

Edit: You need to enclose reserved keywords with '[]' in MS Access

edited Aug 15, 2013 at 16:54

answered Aug 13, 2013 at 16:06

Praveen Prasannan

7,15310 gold badges53 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VB.NET LEARNER Over a year ago

Note that name is a reserved word in Ms Access

Collectives™ on Stack Overflow

sql statement to join three tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related