March 22, 2021 at 11:31 pm
I have a table that selects an ID Username from the user's Loginname and another ID that gets that info from a program dropdown. The original design saved only the LoginName. Later the additional filed was added for users that login with a generic login. My problem now is how do I display the actual user's name from a UserLookup table from either of these main tabled IDs? For now I am just JOINing the ID from the loginname.
SELECT tblInProcessRejectsUsage.UsageDate AS [Search Date], tblOperator.OperatorName, tblInProcessRejectsUsage.SearchItem
FROM tblInProcessRejectsUsage INNER JOIN
tblOperator ON tblInProcessRejectsUsage.UserName = tblOperator.BadgeID
I tried including tblOperator again with the other column, but was unsuccessful. Is there a way to do this?
Maintable:
UserName
MachineName
UsageDate
BadgeID
SearchItem
tblOperator:
OperatorName
BadgeID
March 23, 2021 at 2:43 pm
I think this falls under an "it depends" scenario. If the users are logging in with a generic account and that is all the information that SQL Server has, then there is no way to get the UserName. I am also mildly confused about your query.
This is your query (slightly reformatted to emphasize my confusion on the last line):
SELECT tblInProcessRejectsUsage.UsageDate AS [Search Date], tblOperator.OperatorName, tblInProcessRejectsUsage.SearchItem
FROM tblInProcessRejectsUsage
INNER JOIN tblOperator
ON tblInProcessRejectsUsage.UserName = tblOperator.BadgeID
That join confuses me. Username from the main table should match BadgeID on the operator table? I would expect BadgeID to match BadgeID, no?
But without a WHERE clause, you are going to be grabbing ALL users. If your query OR application knows the user name, you can do a WHERE UserName = @UserName. If neither know this, then you will need to modify the application to grab the user name so it can pass it over to the SQL side.
On a side note, is there a difference between "OperatorName" and "UserName"? The reason I ask is if they are the same, the tblOperator table is redundant as all the data you care about is in the Maintable.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 23, 2021 at 3:42 pm
If they log into SQL initially as themselves, ORIGINAL_LOGIN() might give you their own name rather than the generic name. It may not, but it's worth a try first.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 24, 2021 at 3:01 pm
As I explained in my post, our users login to their PCs by either using a Generic Account Name or if they have their own login, by their BadgeID. If they login with their BadgeID, the UserName column equals the BadgeID. In this case, the JOIN needs to get their Actual Name (OperatorName) from tblOperator. If they login to the computer using the Generic Login, the UserName is useless (doesn't identify the Operator). In this case the BadgeID from tblInProcessRejectsUsage needs a JOIN to get the Actual Name from tblOperator. I just can't figure out how to get both. I either get a JOIN which maps the Username from tblInProcessRejectsUsage to the OperatorName or a JOIN that maps the BadgeID from tblInProcessRejectsUsage to the OperatorName. I can't figure out how to get both...
March 24, 2021 at 3:53 pm
Lots of ways to skin this cat. You can join to the tblOperator table twice (one of them should be a match. ) and use COALESCE to get the result from the successful join.
SELECT tblInProcessRejectsUsage.UsageDate AS [Search Date]
, OperatorName = coalesce(tblOperator.OperatorName, t2.OperatorName)
, tblInProcessRejectsUsage.SearchItemFROM tblInProcessRejectsUsage
LEFT JOIN tblOperator ON tblInProcessRejectsUsage.UserName = tblOperator.BadgeID
LEFT JOIN tblOperator t2 on tblInProcessRejectsUsage.UserName = t2.OperatorName
WHERE tblOperator.BadgeID is not null or t2.OperatorName is not null
Alternatively, you can UNION two queries, each of which uses a different join.
SELECT tblInProcessRejectsUsage.UsageDate AS [Search Date], tblOperator.OperatorName, tblInProcessRejectsUsage.SearchItemFROM tblInProcessRejectsUsage
JOIN tblOperator ON tblInProcessRejectsUsage.UserName = tblOperator.BadgeID
UNION ALL
SELECT tblInProcessRejectsUsage.UsageDate AS [Search Date], tblOperator.OperatorName, tblInProcessRejectsUsage.SearchItemFROM tblInProcessRejectsUsage
JOIN tblOperator ON tblInProcessRejectsUsage.UserName = tblOperator.OperatorName
Finally, you can join using an IN clause.
SELECT tblInProcessRejectsUsage.UsageDate AS [Search Date], tblOperator.OperatorName, tblInProcessRejectsUsage.SearchItemFROM tblInProcessRejectsUsage
JOIN tblOperator ON tblInProcessRejectsUsage.UserName in (tblOperator.BadgeID, tblOperator.OperatorName)
There may be more solutions I can't think of off the top of my head. I favor the IN clause, but you should test them to see which runs best for you. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 25, 2021 at 5:03 pm
Awesome! I like the last one. Thanks for the examples. I'm pretty sure I can use all of them in the future...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply