Annoying SQL problem - CI forums are the best :)

[eluser]obiron2[/eluser]
Sorry, forgot to mention that it is MSSQL not MySQL,

MSSSQL uses 'top n' rather than limit.

When the query in in the join clause, selecting top n restricts the data returned before the join so you will normally get a null result when you try to join, and you cannot use values from the main query as variables in the same way you would in a sub-query.

I think the only way to do it is to build in-memory hash tables and update them with the information I need.

Obiron