Solved

SQL question

Posted on 2001-08-12
3
180 Views
Last Modified: 2011-09-20
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
Comment
Question by:pin_plunder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 21

Expert Comment

by:ziolko
ID: 6378612
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
 
LVL 1

Accepted Solution

by:
ntony earned 100 total points
ID: 6379426
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
 

Author Comment

by:pin_plunder
ID: 6382701
Your code worked just as I wanted to.
THANKS for your help. And thanks Zoilko too.

paul.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question