Return either row by condition (hard to explain)

I have a quite simple requirement, but I can’t find a "simple" and clean/elegant solution. I have a working TVF, but I’m trying to find a way of doing the same thing but in one SELECT-statement.

Business requirement:
I have a cross reference table that consists of four columns. Internal field, supplier field, template name and version.

There is a general mapping between internal field and supplier field for each template with base version 0 (zero).

Now, if the business wants to have s specific mapping for a given reference, i simply want to add a copy of the base mapping and add the version number.

When I propcess my data, I want to take the combination for that specific version AND all other generic mappings EXCEPT the generic ones for which I have a specific version.

Currently I do this within a TVF and supply two variables; version and template name.

Here are some sample code that I’m using today and it works.

Note:
The TVF inserts the result into a table variable and returns it since the TVF can’t handle the code below as-is.

Declare @tbl As Table(
                         FieldId         Int        Not Null
                       , SupplierFieldId Int        Not Null
                       , TemplateName    Varchar(6) Not Null
                       , TemplateVersion Int        Null
                       ,
                       Unique(
                                 FieldId
                               , SupplierFieldId
                               , TemplateName
                               , TemplateVersion
                             )
                     );

Declare @version Int = 0;
Declare @template Varchar(6) = 'Small';

Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(1, 2, 'Big', 0);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(1, 3, 'Big', 16);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(2, 4, 'Small', 0);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(3, 5, 'Small', 0);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(3, 5, 'Big', 0);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(4, 5, 'Big', 15);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(4, 7, 'Small', 16);
Insert Into @tbl(FieldId, SupplierFieldId, TemplateName, TemplateVersion)Values(2, 4, 'Small', 14);

-- Part of my TVF
If @version > 0
    Begin
        Select  t.FieldId
              , t.SupplierFieldId
              , t.TemplateVersion
        From    @tbl As t
        Where
                t.FieldId Not In(
                                    Select  FieldId
                                    From    @tbl
                                    Where
                                            TemplateVersion  = @version
                                            And TemplateName = @template
                                )
                And t.TemplateVersion = 0
                And TemplateName      = @template
        Union
        Select  t.FieldId
              , t.SupplierFieldId
              , t.TemplateVersion
        From    @tbl As t
        Where
                t.TemplateVersion = @version
                And TemplateName  = @template;
    End;
Else
    Begin
        Select  FieldId
              , SupplierFieldId
              , TemplateVersion
        From    @tbl
        Where
                TemplateVersion  = 0
                And TemplateName = @template;
    End;
-- End of TVF-code

So, the result of the code above is:

If I change @version to 16, this is the result:

If I have @version = 16 and change template name to @template = ‘Big, this is the result:

For the last result you can see that there are two entries (inserted into @tbl) with the same internal field id, but they have different supplier id. The template is the same (‘Big’), but there is a specific entry if version is 16.

I tried different joins, CTE, RANK etc but this was the "only" solution that I could come up with.

Performance is not an issue since the reference table only consists of just under 1500 rows.

So, in simple words: I want to select all references for a specific version and the generic ones, but not the generic ones for which there are specific ones given my supplied version number.

>Solution :

Maybe i’m missing something but this looks like a simple WHERE NOT EXISTS:

SELECT  *
FROM    @tbl t
WHERE   TemplateName = @template
AND (
    TemplateVersion = @version
    OR 
    (
        TemplateVersion = 0
        AND NOT EXISTS(
            SELECT  1
            FROM    @tbl t2
            WHERE   t2.templatename = t.templatename
            AND t2.TemplateVersion = @version
            AND t2.fieldid = t.fieldid
        )
    )
)

The outputs seem to match your test data