Re: [sqlite] INNER JOIN, JOIN Confusin
Thanks you for all your help! FMI: Is it possible for it to return not distinct pairs also? Thanks again! Igor Tandetnik wrote: > > Cnichols <cnichol...@gmail.com> wrote: >> Ok I also tried to think about it from a different approached and >> didnt get the result I wanted ... >> >> SELECT COUNT() FROM Temp >> WHERE Id IN (SELECT QuestionId FROM Stats WHERE SessionId = 7) >> >> I expected that to return 46 results .. it returned 29? ... also kind >> of confusing > > This means you have multiple records in Stats containing the same > (SessionId, QuestionId) pair. There are 46 records total, but only 29 > distinct pairs. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/INNER-JOIN%2C-JOIN-Confusin-tp21676221p21688942.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INNER JOIN, JOIN Confusin
I am trying to keep my thoughts as organized as possible. I want to refresh and try to simplify my question ... The following produces 52 results, which consists of 52 questions that where incorrect from session 6, the previous session, we are now in a new session, session 7 SELECT COUNT() FROM Questions Q LEFT JOIN Stats S ON S.QuestionId = Q.Id WHERE S.SessionId = 6 AND S.Correct = 0 Now while in session 7 46 of those questions have been asked but a question may have been asked twice (which is fine) The question is How do I filter the result of 52 to just select only questions that have not been asked (questions that have not been recorded in stats with a session of 7)? Cnichols wrote: > > Ok I also tried to think about it from a different approached and didnt > get the result I wanted ... > > SELECT COUNT() FROM Temp > WHERE Id IN (SELECT QuestionId FROM Stats WHERE SessionId = 7) > > I expected that to return 46 results .. it returned 29? ... also kind of > confusing > What I was thinking that happening there is that We are looping through > each row in Temp and seeing if that Id is present in Stats table with a > session of 7 > since Stats has 46 results with 7 and ALL those QuestionIds assoc with 7 > are in the temp (id) table I don not understand why I would also get only > 29 > > > Cnichols wrote: >> >> I expected it to return 46 results, not fewer as you mentioned, except it >> returns 51 which I am trying to figure out why ... there is only one >> duplicate in Temp, but why would that still produce more results then 46? >> >> Igor Tandetnik wrote: >>> >>> "Cnichols" <cnichol...@gmail.com> wrote >>> in message news:21685825.p...@talk.nabble.com >>>> Stats does contain 46 qids with sids of 7 >>>> there is one duplicate question now that i look harder in temp which >>>> is ok because 289 I forgot to mention that id in temp is not a key, >>>> this is because there is a test mode to ask random questions that >>>> aren't unique (so 289 was asked twice). >>>> >>>> but if i did this >>>> SELECT Count() FROM Stats S >>>> LEFT JOIN Temp T ON T.Id = S.QuestionId >>>> WHERE S.SessionId = 7 >>>> >>>> Shouldn't only still return 46 rows and not 51 since stats in on the >>>> left and we are only getting associated data from the right? >>> >>> LEFT JOIN produces all the same rows as INNER JOIN, plus some rows from >>> left table that don't have a match in the right one. Thus, LEFT JOIN >>> cannot possibly generate fewer rows than a corresponding INNER JOIN. I'm >>> not sure why you expect it to. >>> >>> Igor Tandetnik >>> >>> >>> >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> > > -- View this message in context: http://www.nabble.com/INNER-JOIN%2C-JOIN-Confusin-tp21676221p21687985.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INNER JOIN, JOIN Confusin
Ok I also tried to think about it from a different approached and didnt get the result I wanted ... SELECT COUNT() FROM Temp WHERE Id IN (SELECT QuestionId FROM Stats WHERE SessionId = 7) I expected that to return 46 results .. it returned 29? ... also kind of confusing What I was thinking that happening there is that We are looping through each row in Temp and seeing if that Id is present in Stats table with a session of 7 since Stats has 46 results with 7 and ALL those QuestionIds assoc with 7 are in the temp (id) table I don not understand why I would also get only 29 Cnichols wrote: > > I expected it to return 46 results, not fewer as you mentioned, except it > returns 51 which I am trying to figure out why ... there is only one > duplicate in Temp, but why would that still produce more results then 46? > > Igor Tandetnik wrote: >> >> "Cnichols" <cnichol...@gmail.com> wrote >> in message news:21685825.p...@talk.nabble.com >>> Stats does contain 46 qids with sids of 7 >>> there is one duplicate question now that i look harder in temp which >>> is ok because 289 I forgot to mention that id in temp is not a key, >>> this is because there is a test mode to ask random questions that >>> aren't unique (so 289 was asked twice). >>> >>> but if i did this >>> SELECT Count() FROM Stats S >>> LEFT JOIN Temp T ON T.Id = S.QuestionId >>> WHERE S.SessionId = 7 >>> >>> Shouldn't only still return 46 rows and not 51 since stats in on the >>> left and we are only getting associated data from the right? >> >> LEFT JOIN produces all the same rows as INNER JOIN, plus some rows from >> left table that don't have a match in the right one. Thus, LEFT JOIN >> cannot possibly generate fewer rows than a corresponding INNER JOIN. I'm >> not sure why you expect it to. >> >> Igor Tandetnik >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- View this message in context: http://www.nabble.com/INNER-JOIN%2C-JOIN-Confusin-tp21676221p21687480.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INNER JOIN, JOIN Confusin
I expected it to return 46 results, not fewer as you mentioned, except it returns 51 which I am trying to figure out why ... there is only one duplicate in Temp, but why would that still produce more results then 46? Igor Tandetnik wrote: > > "Cnichols" <cnichol...@gmail.com> wrote > in message news:21685825.p...@talk.nabble.com >> Stats does contain 46 qids with sids of 7 >> there is one duplicate question now that i look harder in temp which >> is ok because 289 I forgot to mention that id in temp is not a key, >> this is because there is a test mode to ask random questions that >> aren't unique (so 289 was asked twice). >> >> but if i did this >> SELECT Count() FROM Stats S >> LEFT JOIN Temp T ON T.Id = S.QuestionId >> WHERE S.SessionId = 7 >> >> Shouldn't only still return 46 rows and not 51 since stats in on the >> left and we are only getting associated data from the right? > > LEFT JOIN produces all the same rows as INNER JOIN, plus some rows from > left table that don't have a match in the right one. Thus, LEFT JOIN > cannot possibly generate fewer rows than a corresponding INNER JOIN. I'm > not sure why you expect it to. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/INNER-JOIN%2C-JOIN-Confusin-tp21676221p21686280.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INNER JOIN, JOIN Confusin
Thanks for the reply. Stats does contain 46 qids with sids of 7 there is one duplicate question now that i look harder in temp which is ok because 289 I forgot to mention that id in temp is not a key, this is because there is a test mode to ask random questions that aren't unique (so 289 was asked twice). but if i did this SELECT Count() FROM Stats S LEFT JOIN Temp T ON T.Id = S.QuestionId WHERE S.SessionId = 7 Shouldn't only still return 46 rows and not 51 since stats in on the left and we are only getting associated data from the right? (Stats can contain multiple questionids also for the same session if ask unique was enable) Igor Tandetnik wrote: > > "Cnichols" <cnichol...@gmail.com> wrote > in message news:21676221.p...@talk.nabble.com >> What i am trying to do right now is select the questions that have >> been asked for the current session (ie 7) >> Stats - contains 46 rows with sessionid of 7 >> Temp - holds 52 missed questions from the previous session (6) >> >> example - this works perfect; it returns 46 results >> >> SELECT COUNT() FROM Stats S >> INNER JOIN Questions T ON S.QuestionId = T.Id >> WHERE S.SessionId = 7 >> >> but if i do this >> -- >> SELECT COUNT() FROM Stats S >> INNER JOIN Temp T ON S.QuestionId = T.Id >> WHERE S.SessionId = 7 >> >> 51 results are returned? I do not understand why? > > Temp contains duplicate values in Id field. Or else, it's not true that > Stats contains 46 records with SessionId=7 - confirm by running > > SELECT COUNT() FROM Stats S > WHERE S.SessionId = 7; > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/INNER-JOIN%2C-JOIN-Confusin-tp21676221p21685825.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INNER JOIN, JOIN Confusin
I have stumped myself with this sql goal. With this statement I am working with 3 tables Stats - contains question asked history and if it was answered correcty sessionid questionid correct Questions - contains a list questions and mul choice answers and answer id - pk . etc. Temp - schema is exactly like questions except contains missed questions from a previous defined sessionid What i am trying to do right now is select the questions that have been asked for the current session (ie 7) Stats - contains 46 rows with sessionid of 7 Temp - holds 52 missed questions from the previous session (6) example - this works perfect; it returns 46 results SELECT COUNT() FROM Stats S INNER JOIN Questions T ON S.QuestionId = T.Id WHERE S.SessionId = 7 but if i do this -- SELECT COUNT() FROM Stats S INNER JOIN Temp T ON S.QuestionId = T.Id WHERE S.SessionId = 7 51 results are returned? I do not understand why? My overall goal is to select a random unique question from Temp that has not been asked (so its not in stats for current session) and ORDER BY Random() LIMIT 1 Any help would be greatly appreciated! -- View this message in context: http://www.nabble.com/INNER-JOIN%2C-JOIN-Confusin-tp21676221p21676221.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] REPLACE INTO Help Once Again
I was not aware of composite keys so I quickly learned what they where and I
have decided to contemplate my database design. I searched for performance
issues with using composite keys and I was not able to find much information
related to SQLite.
Igor Tandetnik wrote:
>
> Cnichols <[EMAIL PROTECTED]> wrote:
>> REPLACE INTO (AccountId, MemberId, SessionId, State)
>> SELECT (M.Id || '-' || S.Id), M.Id, S.Id, 4
>> From Sessions AS S, Members AS M
>> LEFT JOIN AccountState AS AST ON S.Id = AST.SessionId
>> WHERE S.OverdueHandled = 0
>> AND S.Grace < DATE('NOW')
>> --AND AST.State != 1
>
> Right off the bat, I don't see Members table being joined to anything.
> Do you really mean to use a full cartesian product?
>
>> Based on the nature of how REPLACE INTO works I created the AccountID
>> field which is just unique text so a replace can occur.
>
> You don't need an artificial single field for that. You can declare a
> composite key or index, e.g.
>
> create table AccountState (
> MemberId, SessionId, State,
> PRIMARY KEY (MemberId, SessionId)
> )
>
>> To save on diskspace I only keep the alert only when the session has
>> been paid or becomes dilequent so for the other X days there is no
>> need to keep neutral status in the alert table.
>> So a record of an account will not be there unless paid or dilequent
>> (which includes due, grace, overdue, cumalative) so I need to insert
>> a record if its not there but I don't want to replace and paid
>> accounts, but i can overwrite all the other dilquent with a high
>> predecesor
>
> This narrative assumes that the reader knows a) banking terminology; b)
> the exact way it is represented in your data model. You are much likely
> to get help if you restate all these conditions in terms of your tables
> and fields.
>
> Igor Tandetnik
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Help-Once-Again-tf2745497.html#a7666128
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] REPLACE INTO Help Once Again
First off I want to thanks everybody that help me learn the greatness of
REPLACE INTO I have crafted some very complex statements that ended saving
mass amounts of time programming and much more efficient.
Onto the new problem.
I am attempting to INSERT OR REPLACE rows into an AccountState like so
REPLACE INTO (AccountId, MemberId, SessionId, State)
SELECT (M.Id || '-' || S.Id), M.Id, S.Id, 4
>From Sessions AS S, Members AS M
LEFT JOIN AccountState AS AST ON S.Id = AST.SessionId
WHERE S.OverdueHandled = 0
AND S.Grace < DATE('NOW')
--AND AST.State != 1
Based on the nature of how REPLACE INTO works I created the AccountID field
which is just unique text so a replace can occur.
To save on diskspace I only keep the alert only when the session has been
paid or becomes dilequent so for the other X days there is no need to keep
neutral status in the alert table.
So a record of an account will not be there unless paid or dilequent (which
includes due, grace, overdue, cumalative) so I need to insert a record if
its not there but I don't want to replace and paid accounts, but i can
overwrite all the other dilquent with a high predecesor
So if I am just being dumb about this please tell me,
Thanks!
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Help-Once-Again-tf2745497.html#a7660162
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
So GROUP BY will separate the the grouping done by sum()? So that also means
I could factor in all discounts not just static ones? Like so? Modifier 0 =
Percent Of and 1 = Static amount? Also which statement is going to be more
efficient the UPDATE or the REPLACE?
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, MA.Balance -
SUM((CASE WHEN D.Modifier == 0 THEN (D.Amount / 100.00 * 500) ELSE D.Amount
END))
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 wrote:
>
> 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]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7632401
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite_open
I disagree I myself see this as a feature. I think the programmer should be responsible for file checking because if it is not a SQLite database SQLite will inform you. Dave Dyer wrote: > > > It would have saved me 2 days of chasing bogons if sqlite_open > had an alternative form which only worked if the database file > exists and is well-formed. > > The fact that a mistyped file name becomes a new database is a bug. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > -- View this message in context: http://www.nabble.com/sqlite_open-tf2735598.html#a7631881 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
So the sum() function brings the multiple discounts together for a member in
the UPDATE statement?
So I have a question regarding this then the Discount.Modifier stands for 0
= percent of a value or 1 = static
The update you constructed would work for all static values since sums
groups all the numerics together.
So I assume if I used sum() for percent of values it would collect the sum
of all values calculated like so for percent of modifier?
sum(D.Amount / 100 * [C#VALUE])
Also would it be logical to have separate Updates (One for Static, One for
Percent of Value) or would it be more logical to try to achieve this by
using ifnull and nullif on the D.Modifier to get the correct sum function()?
Igor Tandetnik wrote:
>
> Cnichols <[EMAIL PROTECTED]> wrote:
>> I have a complex SQL statement which attempts to update all Member
>> Accounts balance with a discount if there anniversary falls between
>> last handled date and the current day.
>>
>>
>> REPLACE INTO MemberAccounts (MemberId, Balance)
>>
>> SELECT DA.MemberId, (MA.Balance - D.Amount) AS 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
>>
>>
>> The SELECT statement will return multiple rows. A member may have
>> more than one discount that can be applied. When this case occurs
>> the last row for that member is the only one that actually executes.
>> The rows before that are not replacing the value of BALANCE only the
>> last row for that member will affect the balance.
>
> But of course. You want something like this:
>
> REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT DA.MemberId, MA.Balance - ifnull(
> (SELECT sum(D.Amount)
> FROM Discounts AS D, Members AS M
> WHERE
> DA.DiscountId = D.Id AND
> DA.MemberId = M.Id AND
> 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
> ), 0)
> FROM DiscountsApplied AS DA
> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
>
>
> Or perhaps
>
> UPDATE MemberAccounts SET Balance = Balance - ifnull(
> (SELECT 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
> WHERE
> DA.MemberId = MemberAccounts.MemberId AND
> 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
> ), 0)
>
> Igor Tandetnik
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7631805
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
Thanks alot the UPDATE works great! Although the REPLACE INTO still only
executes the last row of a multi discount.
Igor Tandetnik wrote:
>
> Cnichols <[EMAIL PROTECTED]> wrote:
>> I have a complex SQL statement which attempts to update all Member
>> Accounts balance with a discount if there anniversary falls between
>> last handled date and the current day.
>>
>>
>> REPLACE INTO MemberAccounts (MemberId, Balance)
>>
>> SELECT DA.MemberId, (MA.Balance - D.Amount) AS 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
>>
>>
>> The SELECT statement will return multiple rows. A member may have
>> more than one discount that can be applied. When this case occurs
>> the last row for that member is the only one that actually executes.
>> The rows before that are not replacing the value of BALANCE only the
>> last row for that member will affect the balance.
>
> But of course. You want something like this:
>
> REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT DA.MemberId, MA.Balance - ifnull(
> (SELECT sum(D.Amount)
> FROM Discounts AS D, Members AS M
> WHERE
> DA.DiscountId = D.Id AND
> DA.MemberId = M.Id AND
> 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
> ), 0)
> FROM DiscountsApplied AS DA
> LEFT JOIN MemberAccounts AS MA ON DA.MemberId = MA.MemberId
>
>
> Or perhaps
>
> UPDATE MemberAccounts SET Balance = Balance - ifnull(
> (SELECT 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
> WHERE
> DA.MemberId = MemberAccounts.MemberId AND
> 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
> ), 0)
>
> Igor Tandetnik
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7630798
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] REPLACE INTO Only Executes last SELECT?
REPLACE is just an alias for INSERT OR REPLACE.
This SQL Statement works off a Primary key as stated here.
http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSqlUpdateWithFrom
I have an SQL statement that works using this concept.
This SQL Statement will update all accounts for members with an anniversary
betweens dates by 100. Insert will fail because of the Primary Key and the
Replace will occur.
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT M.Id, (MA.Balance + 100)
FROM MemberAccounts AS MA
LEFT JOIN Members AS M ON MA.MemberId = M.Id
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'))
Although for the previous SQL Statement the replace is not occuring for each
row (if multiple discount) only the last row for that member.
Clark Christensen wrote:
>
> I don't think SQLite supports "REPLACE INTO..." I'm pretty sure "REPLACE"
> is a conflict action in SQLite.
>
> Perhaps "INSERT OR REPLACE INTO MemberAccounts (MemberId, Balance)
> SELECT..." will do what you want?
>
> -Clark
>
> - Original Message
> From: Cnichols <[EMAIL PROTECTED]>
> To: sqlite-users@sqlite.org
> Sent: Thursday, November 30, 2006 2:22:19 PM
> Subject: [sqlite] REPLACE INTO Only Executes last SELECT?
>
>
> I have a complex SQL statement which attempts to update all Member
> Accounts
> balance with a discount if there anniversary falls between last handled
> date
> and the current day.
>
>
> REPLACE INTO MemberAccounts (MemberId, Balance)
>
> SELECT DA.MemberId, (MA.Balance - D.Amount) AS 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
>
>
> The SELECT statement will return multiple rows. A member may have more
> than
> one discount that can be applied. When this case occurs the last row for
> that member is the only one that actually executes. The rows before that
> are not replacing the value of BALANCE only the last row for that member
> will affect the balance.
>
> Is there a way I can accumalate the discounts for a member and apply it to
> their balance with an SQL statement? Or will this have to be done
> programmtically with SELECT and then nested in a transaction UPDATES?
> --
> View this message in context:
> http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7629298
> Sent from the SQLite mailing list archive at Nabble.com.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7630506
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] REPLACE INTO Only Executes last SELECT?
I have a complex SQL statement which attempts to update all Member Accounts
balance with a discount if there anniversary falls between last handled date
and the current day.
REPLACE INTO MemberAccounts (MemberId, Balance)
SELECT DA.MemberId, (MA.Balance - D.Amount) AS 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
The SELECT statement will return multiple rows. A member may have more than
one discount that can be applied. When this case occurs the last row for
that member is the only one that actually executes. The rows before that
are not replacing the value of BALANCE only the last row for that member
will affect the balance.
Is there a way I can accumalate the discounts for a member and apply it to
their balance with an SQL statement? Or will this have to be done
programmtically with SELECT and then nested in a transaction UPDATES?
--
View this message in context:
http://www.nabble.com/REPLACE-INTO-Only-Executes-last-SELECT--tf2734721.html#a7629298
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
