Subject Re: [IBO] SQL Problems Svein Erling Tysvær 2002-04-04T14:37:29Z
Alcir,
I think I almost understood your problem. What I didn't quite catch, was
which table DiscoutItSale was in and whether Sale only contained discounts
for the items which were in ItSale. I gave a suggestion which I think you
can use as a start, but I'll repeat it and add a few others.

My original suggestion assumed DiscountItSale was in ItSale and that Sale
only contained DiscountSale for items in ItSale:

SELECT SUM(b.PrItSale*b.QtItSale-DiscountItSale)-
(SELECT (SUM(a.DiscountSale) FROM Sale a)
FROM ItSale b

If there are items in Sale that isn't in ItSale you have to add a little bit:

SELECT SUM(b.PrItSale*b.QtItSale-DiscountItSale)-
(SELECT (SUM(a.DiscountSale) FROM Sale a
WHERE EXISTS(SELECT 1 FROM ItSale c WHERE c.CodSale = a.CodSale)
FROM ItSale b

And if DiscountItSale in addition is in Sale, things start to get messy (I
hope this isn't the case, if so you might want to redesign your solution):

SELECT SUM(b.PrItSale*b.QtItSale-a.DiscountItSale)-
(SELECT (SUM(c.DiscountSale) FROM Sale c
WHERE EXISTS(SELECT 1 FROM ItSale d WHERE d.CodSale = c.CodSale)
FROM Sale a
JOIN ItSale b ON b.CodSale = a.CodSale

In short, for the discounts you give on an item basis (DiscountItSale) use
it in your original sum (as you do). For items that you want subtracted
once only (DiscountSale) use a subselect. As you have discovered, using
JOIN will deduct your discounts several times.

Please tell if there's something I don't understand. I will not be online
until tomorrow, but plenty of others will try to help you if they
understand what you really want to do.

But I think you should consider moving this question to
ib-support@yahoogroups.com - it really isn't an IBO question.

Set