I have discovered a way to do JOINs by vague set of parameters.
The interesting thing about it, is that you can use it in different cases, as "TOP 10" join, "most used" etc.

Here it is:
SELECT * FROM #Tab1
JOIN #Tabl2 ON
#Tab2.ID=
(
SELECT TOP 1 ID from #Tab2 as T2
WHERE
#Tab1.DeviceID LIKE T2.Prefix + '%'
ORDER BY legth(T2.Prefix) DESC
)

First of all, we use "dynamic" LIKE statement, where we compare #Tab1.DeviceID with Prefix from #Tab2, looking for  Prefix + '%' match, i.e. if we have "123" in Prefix, then LIKE will check #Tab1.DeviceID LIKE '123%'.
Then, we order matche's results by length of prefix - so we get longest prefix on the top (best match).
In the end, we take just the best matche's ID (by TOP 1) and compare it with #Tab2.ID in the main query.
Voila, now we have joined #Tab2 on "best prefix match".



Now, let's do the "TOP 10" report.
Say, we have departments in Tab1 and sub-departments in Tab2 with costs. We want to get 10 top subdepartments based on their cost, for each main department.

SELECT * FROM #Tab1
JOIN #Tabl2 ON
#Tab2.ID=
(
SELECT TOP 10 ID from #Tab2 AS T2
WHERE
#Tab1.ID=T2.ParentID
ORDER BY T2.Cost ASC
)