On 16/09/10 11:34, Steve Ellenoff wrote: > SELECT u.username, l.lg_in, l.lg_out, ; > (SELECT MIN(lg2.lg_in)-1 ; > FROM loginhis lg2 ; > WHERE u.userid = lg2.userid AND ; > !EMPTY(lg2.lg_out) AND lg2.lg_in >= loginhis.lg_in AND ; > YEAR(lg2.lg_in)=2008 AND MONTH(lg2.lg_in)<3 AND DAY(l2.lg_in) < 25 ; > ) AS lg_out2; > FROM loginhis L; > LEFT OUTER JOIN users u; > ON u.userid = l.userid; > WHERE YEAR(l.lg_in)=2008 AND MONTH(l.lg_in)<3 AND DAY(l.lg_in) < 25 > >
In the subquery you are using "loginhis.lg_in" instead of "L.lg_in". You should use TtoD(lg2.lg_in) = TtoD(L.lg_in) instead of the last three clauses of your subquery (can't remember if there exists TtoD(), if it does not then use something to compare the date parts). I would use "L.userid = lg2.userid" instead of "u.userid = lg2.userid" in the subquery. In many places in your outer query you are using "l." instead of "L.". Maybe with these corrections the thing will work out. HTH _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
