>>The query as it was written was getting rows from the Items table matching the items in the ModGrpLnk. I think it was not even efficient and most likely it was translated into
>>
>>select some columns from items i where exists (select * from ModGrpLnk m where m.ItemId = i.ItemId and m.GroupId = @GroupId)
>>
>>I didn't actually looked into Profiler, but that's the idea behind my original query, I think.
>>
>>I needed columns from the Items table but sorted in accordance to the ModGrpLnk. So, obviously I could not add the OrderBy clause to my original query as it was coming from the Items.
>>
>>That's why I re-wrote it into JOIN query. I don't see another way to get columns from the Items but sort based on the SortOder from the ModGrpLnk. I think JOIN in this particular case is exactly what I need.
>
>You are still saying join and ModGrpLink but your SQL too says the otherwise. You are only getting from items and only using ModGrpLnk for an exists control. Anyway if you believe it would only work with a join then it should be like that.
I was explaining to you how my original query would have been translated into SQL and why it would not work for me as I needed a column from ModGrpLnk to sort by.
If it's not broken, fix it until it is.
My Blog