Solved

SQL question

Posted on 2001-08-12
3
181 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

738 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