Q:

MSSQL/TSQL Joining against a subquery

I'm analyzing IIS log files from sharepoint and need to match each entry to it's SPWeb.
This SQL code works for a single value (@var1):
DECLARE @var1 varchar(128);         
set @var1 = '/sites/Site1/Subsite1/Subsite2/Documents/marketing.docx';

select
    TOP 1 *,
    charindex(urlstub, @var1) as found 
from 
    spwebs
where
    charindex(urlstub, @var1)  = 1
order by
    urlstub DESC;

I'm looking for a way to get this to work for a tables worth of data instead of just the single variable @var1. 
Example data 
SPwebs:    
/sites/Site1
/sites/Site1/Subsite1
/sites/Site1/Subsite1/Subsite2
/sites/Site2
etc..

IISlog: (this is the table I'd like to take the place of @var1 above) 
/sites/Site1/Subsite1/Subsite2/Documents/marketing.docx
/sites/Site1/Subsite1/Subsite2/Documents/sales.docx
/sites/Site1/Subsite1/Subsite2/Documents/hr.docx
/sites/Site1/research/funding.docx

The expected outcome of the above would be:
Foreach record in the IISLog table:
Find the best/deepest matching record from the spwebs table:
|table                                                    | matchingSPweb                   |
|---------------------------------------------------------| --------------------------------|
| /sites/Site1/Subsite1/Subsite2/Documents/marketing.docx | /sites/Site1/Subsite1/Subsite2/ |
| /sites/Site1/Subsite1/Subsite2/Documents/sales.docx     | /sites/Site1/Subsite1/Subsite2/  |
| /sites/Site1/Subsite1/Subsite2/Documents/hr.docx        | /sites/Site1/Subsite1/Subsite2/  |
| /sites/Site1/research/funding.docx                      | /sites/Site1                     | 

I've tried 
select iislogs2.*, spwebs.urlstub
from 
iislogs2
inner join 
(   
  select TOP 1 urlstub, csURIStem as found 
  from spwebs
   where charindex(urlstub, iislogs2.csUriStem)  = 1
  order by urlstub DESC
)   as x
on x.csuristem = iislogs2.csUriStem

but this just errors, it doesn't seem to understand csUriStem in the context of the subselect statement.

A:

The easiest ways to fix your issue are either to change your current query to use a subquery in the select statement, e.g.:
SELECT iislogs2.*,
       urlstub = (SELECT TOP 1 urlstub FROM spwebs WHERE CHARINDEX(urlstub, iislogs2.csUriStem) = 1 ORDER BY urlstub DESC)
from iislogs2;

... or change your current join to a cross apply, e.g.:
SELECT iislogs2.*, x.urlstub
from iislogs2
cross apply (SELECT TOP 1 urlstub FROM spwebs WHERE CHARINDEX(urlstub, iislogs2.csUriStem) = 1 ORDER BY urlstub DESC) AS x;

EDIT:
The query optimiser might do all sorts of weird sorts and spools, so one option to avoid that might be to use an explicit join with a CTE and then left join this back to your original table. For example:
;WITH CTE AS
(
    SELECT i.csUriStem, s.urlstub, RN = ROW_NUMBER() OVER (PARTITION BY i.csUriStem ORDER BY s.urlstub DESC)
    FROM iislogs2 AS i
    JOIN spwebs AS s
        ON i.csUriStem LIKE s.urlstub + '%'
)
SELECT i.*, c.urlstub
FROM iislogs2 AS i
LEFT JOIN CTE AS c
    ON c.csUriStem = i.csUriStem
    AND c.RN = 1;

Unfortunately, with strings and substrings, it's hard to get an execution plan that is really optimal for what you want to do, but I expect  this sort of query will perform better with indexes than the other two.

