Solved

Query to show username in place of ID

Posted on 2013-06-20
4
158 Views
Last Modified: 2013-06-21
Hello


We have made the following query:

SELECT [HDTicket_Id]
	  ,[HDTicket_Identifier]
	  ,[HDTicket_Subject]
	  ,[HDTicket_Assignee_Benutzer_Id]
      ,[HDTicket_Customer_Benutzer_Id]
      ,[HDTicket_Created]
      ,[HDTicket_Modified] 
      ,[HDTicket_Closed]
      ,[HDStatus_Name]
FROM [macom].[dbo].[dt_HDTicket] 
Join [macom].[dbo].[dt_HDStatus]  
on HDTicket_HDStatus_id=HDStatus_ID
 
Join [macom].[dbo].[Benutzer]
on HDTicket_Assignee_Benutzer_Id=id

where HDTicket_IsClosed = '1' And HDTicket_Closed >= Dateadd(WW, -1, getdate()) and HDStatus_HDProject_id = '7' and HDStatus_Name <> 'Duplicate'

Open in new window


The 2 fields "HDTicket_Assignee_Benutzer_Id" and "HDTicket_Customer_Benutzer_Id" are linked to the table dbo.benutzer's filed ID.

We want to display columns of "HDTicket_Assignee_Benutzer_Id" and "HDTicket_Customer_Benutzer_Id" to display the filed "usersname" from the table dbo.benutzer


Thanks in advanced for the help
0
Comment
Question by:macomsupport
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 250 total points
Comment Utility
Hi.

Is the issue that you have two different dbo.benutzer.ID values? If yes, you will want to have two JOINs. I will show with INNER JOIN, which is appropriate if you must have two valid ID values in HDTicket. If there is a chance one or both of the fields are NULL (i.e., no match), you should use a LEFT JOIN.

SELECT b_c.usersname AS Customer, b_a.usersname AS Assignee
FROM [macom].[dbo].[dt_HDTicket] t
JOIN [macom].[dbo].[Benutzer] b_a ON t.HDTicket_Assignee_Benutzer_Id=b_a.id
JOIN [macom].[dbo].[Benutzer] b_c ON t.HDTicket_Assignee_Benutzer_Id=b_c.id

I hope that helps! The key is the alias to remove ambiguity between the usersname values.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
Comment Utility
I think the intention above was as follows:

SELECT b_c.usersname AS Customer, b_a.usersname AS Assignee
FROM [macom].[dbo].[dt_HDTicket] t
JOIN [macom].[dbo].[Benutzer] b_a ON t.HDTicket_Assignee_Benutzer_Id=b_a.id
JOIN [macom].[dbo].[Benutzer] b_c ON t.HDTicket_Customer_Benutzer_Id=b_c.id

aliases differ in the following, but the intent is exactly the same:
SELECT -- please add the correct aliases to all fields referenced
      [HDTicket_Id]
    , [HDTicket_Identifier]
    , [HDTicket_Subject]
    , t.[HDTicket_Assignee_Benutzer_Id]
    , t.[HDTicket_Customer_Benutzer_Id]
    , ag.usersname AS Assignee
    , cu.usersname AS Customer
    , [HDTicket_Created]
    , [HDTicket_Modified]
    , [HDTicket_Closed]
    , [HDStatus_Name]
FROM [macom].[dbo].[dt_HDTicket] as t
INNER JOIN [macom].[dbo].[dt_HDStatus] as st ON t.HDTicket_HDStatus_id = st.HDStatus_ID
INNER JOIN [macom].[dbo].[Benutzer] as ag    ON t.HDTicket_Assignee_Benutzer_Id = ag.id
INNER JOIN [macom].[dbo].[Benutzer] as cu    ON t.HDTicket_Customer_Benutzer_Id = cu.id
WHERE HDTicket_IsClosed = '1'
    AND HDTicket_Closed >= Dateadd(WW, - 1, getdate())
    AND HDStatus_HDProject_id = '7'
    AND HDStatus_Name <> 'Duplicate'

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Thanks, @PortletPaul! That was a copy and paste error for sure.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
:) it happens
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now