Thanks Ricardo! Originally I was using l.lg_in in the subquery as you recommended, but I was getting a VFP SQL Correlation error so I changed it to loginhist.lg_in to avoid the error.
However, you hit the nail on the head with your other recommendation to use l.userid = lg2.userid. Once I did that I was able to put back the l.lg_in comparison earlier, and it all works as expected. Thanks!! -Steve At 02:03 PM 09/16/2010, you wrote: >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 > > [excessive quoting removed by server] _______________________________________________ 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.
