• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

SQL question

Ok, I've got 2 tables: Customers.db and Orders.db
The fields I need to mention for each table are:

Customers.db
----------------------------------------
|| IdCustomer || Employer ||
----------------------------------------

Orders.db
-------------------------------------------------------------
|| IdCustomer || Price || NOfPayments || PaymentsAlreadyPayed
-------------------------------------------------------------

What I'm supposed to do is to print all the guys whose employer = 11 and add U$14 to their SUM(Price), EVEN IF THEY ARE NOT LISTED IN Orders.db, this means, even if they have not buyed anything.
Ahh, SUM(Price) is conditioned to PaymentsAlreadyPayed < NOfPayments, so here goes an example,

Customers.db
----------------------------------------
|| IdCustomer || Employer ||
----------------------------------------
1168                12
1169                11
1170                11  
1171                11
1172                13
1173                11
1174                11

Orders.db
-------------------------------------------------------------
|| IdCustomer || Price || NOfPayments || PaymentsAlreadyPayed
-------------------------------------------------------------
1168             21.00        5                   2
1169             12.50        6                   6
1169             20.00        4                   3
1669             50.00        3                   0
1170             75.00        4                   1
1171             25.00        6                   6
1171             35.00        3                   1


Output should be like this...

Output
----------------------------------------
IdCustomer || SUM(Price)
----------------------------------------
1169             70 + 14 = 84
1170             75 + 14 = 89
1171             35 + 14 = 49
1173             0  + 14 = 14         // this guy is not in orders.db
1174             0  + 14 = 14         // this guy is not in orders.db

Please send SQL code.

thanks A LOT!!
paul.
0
pin_plunder
Asked:
pin_plunder
1 Solution
 
ziolkoCommented:
select o.IdCustomer, SUM(o.Price)+14
from Orders o JOIN Customers c ON (o.IDCustomer=.IdCustomer)
where c.Employer=11 AND o.PaymentsAlreadyPayed < o.NOfPayments
GROUP BY IdCustomer
ziolko.
P.S. this Q should be in Databases topic
0
 
ntonyCommented:
try this is not very elegant but will do:


select o.idcustomer,sum(o.price)+14 price
from orders o,customers c
where o.idcustomer=c.idcustomer and c.employer=11 and o.NOfPayments>o.PaymentsAlreadyPayed
group by o.idcustomer
union
select idcustomer,14 as price
from customers
where idcustomer not in
(select idcustomer from orders) and employer=11
0
 
pin_plunderAuthor Commented:
Your code worked just as I wanted to.
THANKS for your help. And thanks Zoilko too.

paul.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now