Join/replace ID with Name in a variable column

Question

We currently have a change log that logs any change within our tables on our windows database. The two most important columns in that table are the OldValue and the NewValue columns. When a user changes a TalentCategoryID from one TalentCategoryID to another it shows the change of the ID #. Rather than display the TalentCategoryID I would like to be able to see the Name associated with the TalentCategoryID which I have the name/id relationship in a separate table called TalentCategories.

The problem I am having is that I cannot simply do a JOIN because the TalentCategoryID is in the OldValue and NewValue columns and they are not strictly TalentCategoryID because it is a changelog for multiple tables, columns, and values.

Is there a way to display the OldValue and NewValue as the Name of the TalentCategoryID rather than the ID itself?

Here is the query I have so far:

SELECT T.FirstName, T.LastName, A.TableName, A.ColumnName, A.OldValue, A.NewValue, TC.Name, A.UpdateDate
    FROM [AuditLog] A 
    JOIN Talents T ON A.ModifiedPrimaryKey = T.TalentID
    JOIN TalentCategories TC ON TC.TalentCategoryID = T.TalentCategoryID
    WHERE TableName = 'Talents'
    AND ColumnName = 'TalentCategoryID'

UPDATE 1/31/19 Below

This is the results when I pull the following table AuditLog with the query

SELECT TOP 1000 * FROM dbo.AuditLog WHERE ColumnName = 'TalentCategoryID'

I am attempting to change the OldValue/NewValue columns from ID numbers to the actual Name value given in another table called TalentCategories this table has an ID column and a Name column. The goal is to be able to easily identify when a TalentCategory is changed from one Category NAME to another category NAME not just seeing a change in ID numbers that I don't know what they mean without looking at the TalentCategories table. Below is an example of what I am trying to achieve...

And where does `ModifiedPrimaryKey` come from? Don't you mean NewValue? — Dale K, Jan 30 '19 at 00:25
I have provided a screenshot in the original post that will show you the results of the AuditLog table in which I am referencing most of the data from. I simply what to see the Name associated with the ID numbers in the OldValue/NewValue columns. @DaleBurrell the modifiedprimarykey is the primary key in which I join the talents table in order to get the talents name associate with what their TalentCategoryID was changed too. I will also post a screenshot of what I am trying to achieve. — Schaeffer Warnock, Jan 31 '19 at 23:33
Does this answer your question? [MySQL JOIN to replace IDs with value from another table](https://stackoverflow.com/questions/12431636/mysql-join-to-replace-ids-with-value-from-another-table) — mickmackusa, Oct 04 '21 at 21:57

Dale K · Accepted Answer · 2019-02-01T00:20:08.823

0

I think you just need to left join TalentCategories onto both the old and the new values, taking care to handle the null case. Something like the following:

select
  T.FirstName, T.LastName, A.TableName, A.ColumnName, A.OldValue, A.NewValue
  , coalesce(TC1.[Name], 'Not Set') [Old Category Name]
  , coalesce(TC2.[Name], 'Not Set') [New Category Name]
  , A.UpdateDate
from [AuditLog] A 
inner join Talents T ON A.ModifiedPrimaryKey = T.TalentID
left join TalentCategories TC1 on TC1.TalentCategoryID = A.OldValue
left join TalentCategories TC2 on TC2.TalentCategoryID = A.NewValue
where TableName = 'Talents' and ColumnName = 'TalentCategoryID'

edited Feb 01 '19 at 00:20

answered Jan 30 '19 at 00:27

Dale K

25,246
15
42
71

This query unfortunately was not what I was looking for... After running this query it seems as if it is pulling two different talent FirstName and two different talent LastName on the same row. My goal is to see when a talents category changes from one TalentCategoryID to another TalentCategoryID (which is what the AuditLog table shows) but rather than seeing the ID I want to see the name associated with the TalentCategoryID so that I don't always have to reference another table with the Name... Hope that makes sense. – Schaeffer Warnock Jan 31 '19 at 23:25

Join/replace ID with Name in a variable column

1 Answers1