Returning Two different values from join

  • 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

  • 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

  • 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