Solved

SQL question

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

914 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now