SQLite Forum

Join tables by nearest older date
Thanks again. What's really annoying is that I originally had something close to your last alternative, but couldn't quite get it to work... mostly it kept complaining about "no such column" inside the correlated query (even though I'm certain the column name was correct).

At the time, I put it down to some restriction on what could be used from the outer query within the correlated query, and that's when I asked here.

Having tried your version, I now suspect my original problem was something – that to me appears odd – to do with table aliases. I tend to use these a lot, so I would have had something like

select *
  from WinEvents as WE
  join GameEvents as GE
    on GE.GameID ...

I can't remember exactly the version I tried, but the "seems odd to me" behaviour can be shown by the following much simpler queries:

sqlite> select * from Members as M order by M.FirstName ;
<...produces results as expected...>
sqlite> select * from Members as M order by Members.FirstName ;
Error: no such column: Members.FirstName

Am I right to feel surprised by this? I thought "`...as M`" _allowed_ you to use the (usually) shorter alias, but I didn't think it _insisted_ on using the alias... I feel (fairly) sure I've seen queries that mix-and-match the real and aliased name. (I get the same result using the perhaps more canonical version that omits the "`AS`" keyword).