February 16, 2006 at 11:01 am
Hi,
I have a query that has two different id's from a user table. The Case table has an OpendBy and a AssignedTo field in it. They are lookups on the User table. I need the friendly names from the user table, but don't know how to return 2 different users for each row returned. Here is my SQL statement so far:
Select
Contact.FirstName AS 'PersonCalling',
[Case].CreatedById,
[User].FirstName as 'OpenedBy',
[Case].OwnerId,
[User].FirstName as 'AssignedTo'
FROM [Case]
INNER JOIN
[User] ON
[Case].CreatedById = [User].Id
INNER JOIN
Contact ON
[Case].ContactId = Contact.Id
Any help will be greatly appreciated
Thanks
David
February 16, 2006 at 11:24 am
You need to join to the table twice, assigning aliases to differentiate each instance of the table:
Select
Contact.FirstName AS 'PersonCalling',
[Case].CreatedById,
[UserC].FirstName as 'OpenedBy',
[Case].OwnerId,
[UserA].FirstName as 'AssignedTo'
FROM [Case]
INNER JOIN
[User] As UserC ON [Case].CreatedById = [UserC].Id
INNER JOIN
[User] As UserA ON [Case].AssignedToId = [UserA].Id
INNER JOIN
Contact ON [Case].ContactId = Contact.Id
February 16, 2006 at 11:31 am
PW,
That works great. Thanks for the help. Iv'e never come accross that situation before, so its nice to have a solution.
David
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply