Get Value from 2 Different Tables Depending on Condition

wesborland1234 · March 3, 2021, 2:18am

I have a somewhat complex query, but what I'm trying to accomplish is this:
I have 3 tables with a User_ID column:

a User table [Users]
a table for when a user enters a record [Tagged]
and a table for when a user modifies a record [Tagged_Mod]

The result that I am looking for is a list of actions (entered/modified) and
the users who did the action. Right now, I am getting the actions for records created
or modified, but the user always show the user who ENTERED the record, even on
the "modified" lines.

Here is the query:
Select tg.Tag_ID, tg.USER_ID, tmo.User_ID, u.First_Name+' '+u.Last_Name AS UserName, tg.Proj_Prefix, tg.Proj_Number, tm.Name AS Template, tg.Payee, tg.Payor,
CASE WHEN tmo.Mod_Date IS NOT NULL THEN 'Update' ELSE 'Tag' END AS User_Action,
CASE WHEN tmo.Mod_Date IS NOT NULL THEN tmo.Mod_Date ELSE tg.Date_Created END AS Action_Date
FROM Tagged tg INNER JOIN Users u ON tg.User_ID = u.User_ID
INNER JOIN Files_Split fp ON tg.Project_ID = fp.Project_ID AND tg.Proj_Number = fp.Proj_Number
INNER JOIN Templates tm ON tg.Temp_ID = tm.Temp_ID
INNER JOIN Projects p ON tg.Project_ID = p.Project_ID
FULL OUTER JOIN Tagged_Mod tmo on tmo.Tag_ID = tg.Tag_ID
WHERE u.First_Name NOT LIKE '%test%' AND u.Last_Name NOT LIKE '%test%' AND p.Name NOT LIKE '%temp%' AND p.Name NOT LIKE '%xml%' AND p.Name NOT LIKE '%train%' AND
fp.AttachWith IS NULL AND (tg.Date_Created >= '2021-02-28' OR tmo.Mod_Date >= '2021-02-28')
ORDER BY UserName, Action_Date, Proj_Prefix, Proj_Number;

Tagged and Tagged_Mod also share a Tag_ID that is unique to each record.

A lot of the other stuff in the query is irrelevant.

The result of my query will be two rows, both with that Tag_ID, one as Entered and one as Modified. However, it will show the same User both times, when there are two different user ID's. The row showing the "Modified" action needs to have the Payee and Payor as well.

How do I fix that?

Thanks in advance, I have been stuck on this for a few days now.

yosiasz · March 3, 2021, 4:56am

Welcome.

To help us help you, please provide sample data in the following format

declare @table1 table(col1 int, col2 int) --etc etc

insert into @table1
select 1, 15 union
select 4, 45

otherwise since we do not have access to your sql server, words and images are not sufficient

wesborland1234 · March 3, 2021, 1:15pm

OK, here is some sample data, hopefully I entered this correctly:

declare @Users table(User_ID int, First_Name nvarchar(50), Last_Name nvarchar(50), Admin int, Email nvarchar(100))
declare @Tagged table(Tag_ID int, User_ID int, Date_Created smalldatetime, Payor nvarchar(1000), Payee nvarchar(1000), Proj_Prefix nvarchar(50), Proj_Number int, Project_ID int, Temp_ID int)
declare @Tagged_Mod table(Tag_ID int, User_ID int, Mod_Date smalldatetime, Description nvarchar(4000))
declare @Templates table(Name nvarchar(50), Temp_ID int)
declare @Files_Split table(Project_ID int, Proj_Number int, AttachWith int)
declare @Project table(Project_ID int, Name nvarchar(100))
Insert into @Users (User_ID, First_Name, Last_Name, Admin, Email
Values (163, 'Bob', 'Smith', 4, 'BobSmith@aol.com')
Insert into @Users (User_ID, First_Name, Last_Name, Admin, Email
Values (115, 'Lisa', 'Jones', 4, 'LisaJones@aol.com')
Insert into @Tagged (Tag_ID, User_ID, Date_Created, Payor, Payee, Proj_Prefix, Proj_Number, Project_ID, Temp_ID)
Values (569420, 163, '2021-03-01', 'Walmart', 'Joe Thomas', 'PRE001', 500, 12, 10)
Insert into @Tagged_Mod (Tag_ID, User_ID, Mod_Date, Description)
Values (569420, 115, '2021-03-02', 'Update')
Insert Into @Templates (Name, Temp_ID)
Values ('Check', 10)
Insert into Files_Split (Project_ID, Proj_Number, AttachWith)
Values (500, 12, null)
Insert into Project (Project_ID, Name)
Values (12, 'My Project')

Ifor · March 3, 2021, 6:38pm

As you cannot be bothered to post a cogent test rig you are not going to get tested code.
(You have not posted the query with the table variables, it looks like the numbers in @Files_Split are transposed etc.)

You really need to normalize your schema. With the current schema:

Get rid of the FULL JOIN to @Tagged_Mod.
Put the following at the top of the query:

WITH AllTags
AS
(
	SELECT Tag_ID, [User_ID], Mod_Date, [Description]
	FROM @Tagged_Mod
	UNION ALL
	SELECT Tag_ID, [User_ID], Date_Created. 'Created'
	FROM @Tagged
)

Start the FROM clause with:

FROM AllTags A
	JOIN @Tagged tg ON A.Tag_ID = tg.Tag_ID
	--etc

Adjust the SELECT and WHERE accordingly.

wesborland1234 · March 3, 2021, 10:27pm

I am sorry that I did not post a good test rig. I thought what I typed was coherent, but I was on my way out of the house.
Anyway, I tried your suggestion, but it did not work.
I did figure out a solution though!
The trick is that since you're basing the user name on a User_ID that may come from one of two tables, you need to set the correct User_ID in a new evaluated column in a temporary table, and then join that table to Users (as opposed to doing the JOIN to Users where I was).
I'm going to clean this up a bit, but here is what works:

Select a.Tag_ID, a.Bates_Prefix, a.Bates_Number, a.Template, a.Payee, a.Payor, a.User_Action, a.Action_Date, u.First_Name+' '+u.Last_Name AS Username FROM Users u INNER JOIN
(Select tg.Tag_ID, tg.Bates_Prefix, tg.Bates_Number, tm.Name AS Template, tg.Payee, tg.Payor, 
CASE WHEN tmo.Mod_Date IS NOT NULL THEN 'Update' ELSE 'Tag' END AS User_Action, 
CASE WHEN tmo.Mod_Date IS NOT NULL THEN tmo.Mod_Date ELSE tg.Date_Created END AS Action_Date, 
CASE WHEN tmo.Mod_Date IS NOT NULL THEN tmo.User_ID ELSE tg.USER_ID END AS Person_ID
FROM Tagged tg 
INNER JOIN Files_Split fp ON tg.Project_ID = fp.Project_ID AND tg.Bates_Number = fp.Bates_Number 
INNER JOIN Templates tm ON tg.Temp_ID = tm.Temp_ID 
INNER JOIN Projects p ON tg.Project_ID = p.Project_ID 
FULL OUTER JOIN Tagged_Mod tmo on tmo.Tag_ID = tg.Tag_ID 
WHERE  fp.AttachWith IS NULL AND (tg.Date_Created >= '2021-02-28' OR tmo.Mod_Date >= '2021-02-28')
) a ON a.Person_ID = u.User_ID
ORDER BY UserName, Action_Date, Bates_Prefix, Bates_Number

Thank you for your help!