Selecting nvarchar 'like' in a left join - inner join combination

Question

I currently have a query that returns results based on a dynamic set of conditions

DataTable Items:
    ID   Title
    1    Some title
    2    Some other title
..etc..

.

DataTable Tags:
    ID   Tag
    1    'a1c'
    1    'a1d'
    2    'a2c'
..etc..

My current search Query:

select * from dbo.Items i
LEFT JOIN dbo.tags t
on i.ID = t.ID
INNER JOIN @input in
on (in.[input] = t.Tag or in.[input] is null)

An input would be something like:

DECLARE @input as inputTable_type
INSERT INTO @input VALUES ('a1c')

What I would like to do is use a value like 'a1%' as an input, but when I try that, I get no results (although I do know that a query such as the following does work):

select * from dbo.Items i
INNER JOIN dbo.tags t
on i.ID = t.ID
and t.Tag like ('a1%')

Unfortunately, I want to keep it a static query, and as far as I know, I need the LEFT JOIN - INNER JOIN combination that I have in order to be able to pass VALUES (NULL) into the stored procedure.

Thanks for any thoughts and help, and let me know if I can clarify anything!

Answer 1

Rather than trying to store 'a1%' in your @input table, store just 'a1', then I think the (untested) code below will give you what you want.

select * 
    from dbo.Items i
        LEFT JOIN dbo.tags t
            INNER JOIN @input in
                on in.[input] = left(t.Tag, len(in.[input]))
            on i.ID = t.ID
    where (t.ID is not null or not exists(select 1 from @input))

Answer 2

Since you're already playing funny games with the outer joined table, why not extend the games? The simplest version is to replace the equality with LIKE; the next simplest version is to add an OR term:

SELECT *
  FROM dbo.Items i
  LEFT JOIN dbo.tags t ON i.ID = t.ID
 INNER JOIN @input  in ON (t.Tag = in.[input] OR
                           t.Tag LIKE in.[input] OR
                           in.[input] IS NULL)

The next grade up in sophistication (or do I mean 'complication'?) is to add a second column to the @input table which indicates the comparison to perform:

SELECT *
  FROM dbo.Items i
  LEFT JOIN dbo.tags t ON i.ID = t.ID
 INNER JOIN @input  in ON ((in.[type] = '=' AND t.Tag = in.[input]) OR
                           (in.[type] = '%' AND t.Tag LIKE in.[input]) OR
                           in.[input] IS NULL)

I've chosen to call the extra column 'type' and the value in it is a single character, using '=' to indicate an equality join and '%' to indicate a LIKE join.