• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Tricky Join query referencing same table twice?

Hi all,

In my registration DB I have a "Users" table and a "Nominations" table. The Nomination table holds two ID's (UserID & NominatingUserID), both of which refer to the "UserID" column in the Users table but different rows. Table structures below:

[dbo].[Nominations](
      [NominationID] [int] IDENTITY(1,1) NOT NULL,
      [UserID] [int] NULL,
      [NominatingUserID] [int] NULL,
      [DateNominated] [datetime] NULL,
      [NominationMessage] [varchar](500) NULL,
      [InviteSent] [varchar](20),
      [IsApproved] [varchar](20)
      
      
[dbo].[Users](
      [UserID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
      [UserFirstName] [varchar](200) NULL,
      [UserSurname] [varchar](200) NULL,
      [UserEmailAddress] [varchar](200) NULL,
      [UserJobTitle] [varchar](200) NULL,
      [UserOrganisationName] [varchar](200) NULL,
      
      I need to write a query that will return the contents of the Nominations table but replace the Nominations.UserID & Nominations.NominatingUserID fields with the corresponding Users.UserFirstName & Users.UserSurname entries.
      
      That make sense?
      
      Many tahnsk in advance!
      
      Nell
0
AcclaimNeilr
Asked:
AcclaimNeilr
  • 4
  • 3
2 Solutions
 
chapmandewCommented:
select n.*, u.userfirstname as userfirstname, n.userfirstname as nominatingfirstname
 from nominations n
left join users u on n.userid = u.userid
left join users nom on n.nominatinguserid = nom.userid
0
 
chapmandewCommented:
just FYI...used left join in case there were nulls.  Include any other fields from the outer joined tables as necessary that you want to return.
0
 
AcclaimNeilrAuthor Commented:
wow... fast reply! Ta for that.. have tried but get the following error:

"Msg 207, Level 16, State 1, Line 1
Invalid column name 'userfirstname'."

Attempting to step through, not used this syntax before but is it trying to reference a "userfirstname" & "usersurname" fcolumn in th eNominations table as there are none?

Thanks again,

Nell
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
chapmandewCommented:
its trying to reference the UserFirstName columns from the Users table..post the query you got the error with.
0
 
AcclaimNeilrAuthor Commented:
basically cut and paste yours..

"select n.*, u.userfirstname as userfirstname, n.userfirstname as nominatingfirstname
 from nominations n
left join users u on n.userid = u.userid
left join users nom on n.nominatinguserid = nom.userid"

looks like it referencing th ecorrect table/column names?
0
 
SuperdaveCommented:
Looks like the n.userfirstname should have been nom.userfirstname:

select n.*, u.userfirstname as userfirstname, nom.userfirstname as nominatingfirstname
 from nominations n
left join users u on n.userid = u.userid
left join users nom on n.nominatinguserid = nom.userid
0
 
chapmandewCommented:
my fault...I wrote it wrong.  Should be nom.userfirstname rather than n.userfirstname
0
 
AcclaimNeilrAuthor Commented:
Thanks guys, sorry for late response! Have split as Superdave came in with the amendment but obviously chapmandew provided most of it. that ok?

many thanks again!!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now