Cnichols <[EMAIL PROTECTED]> wrote:
Thanks alot the UPDATE works great! Although the REPLACE INTO still
only executes the last row of a multi discount.
I see. MA to DA is a one-to-many relation, too. You need to move all the
tables into an inner select: the goal is to use SUM aggregate to produce
a single row (or no rows) for each MA.MemberId. Like this:
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance - SUM(D.Amount)
FROM DiscountsApplied AS DA
LEFT JOIN Discounts AS D ON DA.DiscountId = D.Id
LEFT JOIN Members AS M ON DA.MemberId = M.Id
LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
WHERE
DATE(strftime('0001-%m-%d', M.Registered)) > DATE(strftime('0001-%m-%d',
(SELECT RegistrateDate FROM Config)))
AND
DATE(strftime('0001-%m-%d', M.Registered)) <=
DATE(strftime('0001-%m-%d',
'now'))
AND
D.Type = 1 AND D.Modifier = 1
GROUP BY DA.MemberId
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
