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:
[NominationID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[NominatingUserID] [int] NULL,
[DateNominated] [datetime] NULL,
[NominationMessage] [varchar](500) NULL,
[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!