Conditional joining in MS SQL

Question

I have a DB table to store rudimentary history. I'm trying to keep it super generic.

The table looks like:

HistoryID   Action      Table       PrimaryKey  Field           OldValue    NewValue            User                DateTime
--------------------------------------------------------------------------------------------------------------------------------------------
240233      Added       QueueItem   17177       QueueItemID     17177                           XXXXXXXXXXXXXXX 2016-09-16 08:38:58.060
240237      Modified    QueueItem   17177       StatusTypeID    1           2                   XXXXXXXXXXXXXXX 2016-09-16 08:38:59.163
240240      Modified    QueueItem   17177       StatusTypeID    2           3                   XXXXXXXXXXXXXXX 2016-09-16 08:39:00.850
240241      Modified    QueueItem   17177       PackageID       0                               XXXXXXXXXXXXXXX 2016-09-16 08:39:00.850
240249      Modified    QueueItem   17177       StatusTypeID    3           4                   XXXXXXXXXXXXXXX 2016-09-16 08:39:09.207
240256      Modified    QueueItem   17177       StatusTypeID    4           5                   XXXXXXXXXXXXXXX 2016-09-16 08:39:10.163
240257      Modified    QueueItem   17177       OutputDateTime              9/16/2016 8:39:10   XXXXXXXXXXXXXXX 2016-09-16 08:39:10.163

What I am wondering is possible, is to conditionally join depending on the value of Field. I imagine I would have to use t-SQL though I'm a little stuck.

I could do individual selects with a where on the field and then union all my results but wasn't sure if there was a better way?

pseudo-code would be something like:

select h.*, s.[name] from history h 
    some kind of condition that when satified joins the statusType table and if not, then perhaps sets s.[name] to null

I tried something like this, and it works for 'StatusFrom' but not with 'StatusTo' - fails because it cannot convert the date in the last row to an int.

select h.*, 
       so.[Name] as StatusFrom, 
       sn.[Name] as StatusTo 
from history h
left outer join StatusType so 
             on so.StatusTypeID = Convert(int, h.OldValue) 
            AND 1 = case WHEN h.field = 'StatusTypeID' 
                         THEN 1
                         else 0
                    end
left outer join StatusType sn
             on sn.StatusTypeID = Convert(int, h.NewValue) 
            AND 1 = case WHEN h.field = 'StatusTypeID' 
                         THEN 1
                         else 0 
                    end
where primarykey = 17177

I would provide more code example though I am just super stuck.

I guess i only really want the actual join if a condition is met. Is this something that's possible?

Update

Expected result would be:

HistoryID   Action      Table       PrimaryKey  Field           OldValue    NewValue            User                DateTime                StatusFrom      StatusTo
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
240233      Added       QueueItem   17177       QueueItemID     17177                           XXXXXXXXXXXXXXX 2016-09-16 08:38:58.060     NULL            NULL
240237      Modified    QueueItem   17177       StatusTypeID    1           2                   XXXXXXXXXXXXXXX 2016-09-16 08:38:59.163     New             Building
240240      Modified    QueueItem   17177       StatusTypeID    2           3                   XXXXXXXXXXXXXXX 2016-09-16 08:39:00.850     Building        Ready
240241      Modified    QueueItem   17177       PackageID       0                               XXXXXXXXXXXXXXX 2016-09-16 08:39:00.850     NULL            NULL
240249      Modified    QueueItem   17177       StatusTypeID    3           4                   XXXXXXXXXXXXXXX 2016-09-16 08:39:09.207     Ready           Processing
240256      Modified    QueueItem   17177       StatusTypeID    4           5                   XXXXXXXXXXXXXXX 2016-09-16 08:39:10.163     Processing      Done
240257      Modified    QueueItem   17177       OutputDateTime              9/16/2016 8:39:10   XXXXXXXXXXXXXXX 2016-09-16 08:39:10.163     NULL            NULL

Per SWE answer, i tried this too:

select h.*, so.[Name] as StatusFrom, sn.[Name] as StatusTo from history h
    left outer JOIN StatusType so on so.StatusTypeID = Convert(int, h.NewValue)  and h.field = 'statustypeid'
    left outer JOIN StatusType sn on sn.StatusTypeID = Convert(int, h.NewValue)  and h.field = 'statustypeid'
 where primarykey = 17177

I get the same issue - falls over when it hits the date in the newValue field

since you are doing left joins you condition will not leave out any records but only make them null. Is that what you wanted / expected ? — GuidoG, Sep 16 '16 at 14:17
yes. I need to bring back all the history but where there is a field of statusTypeID then i would like to join to the status table to get the text name. — Darren Wainwright, Sep 16 '16 at 14:18
A suggestion would be to add an additional column to the history table with the relevant information from the foreign table, as that value could be changed/deleted too -- history table should be immune to external changes. — Rubens Farias, Sep 16 '16 at 14:32
@RubensFarias - You're absolutely correct. It should be a complete snapshot. Perhaps I will re-work it. I would like to know if my Q is possible though. — Darren Wainwright, Sep 16 '16 at 14:33

score 0 · Answer 1 · answered Sep 16 '16 at 14:21

Joining to dynamic table is not supported.

You have do go through a tsql-stored-procedure with some cursour-logic and build your result set by hand.

OR, if you have only a handfull of possible tables, join everyone and set conditions that only evaluate to true, if you would like to "join" each table.

SELECT * 
FROM a
LEFT JOIN b on a.id = b.id AND a.table = 'b' and a.field = 'id'
LEFT JOIN b on a.id = b.something AND a.table = 'b' and a.field = 'something'
LEFT JOIN c...

Maybe some kind of subselect in the SELECT-statement is better and more easy to read, if you just want one value of some child-table.

Ref your 'Or' statement - that's what I tried to do in my example. I added an update showing my new code and i get the same error — Darren Wainwright, Sep 16 '16 at 14:31

score 0 · Answer 2 · edited May 23 '17 at 10:27

I was so very close with my initial script.

Help from this answer Can I use CASE statement in a JOIN condition? allowed me to come up with this, which gives me my expected results:

select h.*, so.[Name] as StatusFrom, sn.[Name] as statusto from history h
    left outer join StatusType so 
        on 
        case
        when h.field='StatusTypeID' and so.StatusTypeID = Convert(int, h.OldValue)  then 1  
        else 0
        end = 1
    left outer join StatusType sn
        on case
        when h.field='StatusTypeID' and sn.StatusTypeID = Convert(int, h.NewValue)  then 1  
        else 0
        end = 1
 where primarykey = 17177

Conditional joining in MS SQL

2 Answers2