Sql-server – Find these query results

sql servert-sql

Given This

CREATE TABLE [dbo].[ContactPosition](
    [ContactPositionId] [int] NOT NULL,
    [ContactId] [int] NOT NULL,
    [PositionId] [int] NOT NULL,
 CONSTRAINT [PK_ContactPosition] PRIMARY KEY CLUSTERED 
(
    [ContactPositionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Contacts](
    [ContactId] [int] NOT NULL,
    [FirstName] [varchar](256) NOT NULL,
    [LastName] [varchar](256) NOT NULL,
 CONSTRAINT [PK_CONTACTS] PRIMARY KEY CLUSTERED 
(
    [ContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Lookup](
    [Id] [int] NOT NULL,
    [Topic] [varchar](256) NOT NULL,
    [PrimaryContactPosition] [int] NOT NULL,
    [SecondaryContactPosition] [int] NOT NULL,
    [ThirdContactPosition] [int] NOT NULL,
 CONSTRAINT [PK_Lookup] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[ContactPosition] ([ContactPositionId], [ContactId], [PositionId]) VALUES (1, 1, 2)
INSERT [dbo].[ContactPosition] ([ContactPositionId], [ContactId], [PositionId]) VALUES (2, 2, 1)
INSERT [dbo].[ContactPosition] ([ContactPositionId], [ContactId], [PositionId]) VALUES (3, 3, 3)
INSERT [dbo].[ContactPosition] ([ContactPositionId], [ContactId], [PositionId]) VALUES (4, 4, 4)
INSERT [dbo].[ContactPosition] ([ContactPositionId], [ContactId], [PositionId]) VALUES (5, 5, 5)
INSERT [dbo].[ContactPosition] ([ContactPositionId], [ContactId], [PositionId]) VALUES (6, 6, 6)
INSERT [dbo].[ContactPosition] ([ContactPositionId], [ContactId], [PositionId]) VALUES (7, 7, 7)
INSERT [dbo].[Contacts] ([ContactId], [FirstName], [LastName]) VALUES (1, N'John', N'Doe')
INSERT [dbo].[Contacts] ([ContactId], [FirstName], [LastName]) VALUES (2, N'Jane', N'Doe')
INSERT [dbo].[Contacts] ([ContactId], [FirstName], [LastName]) VALUES (3, N'Ed ', N'Doe')
INSERT [dbo].[Contacts] ([ContactId], [FirstName], [LastName]) VALUES (4, N'Ron', N'Doe')
INSERT [dbo].[Contacts] ([ContactId], [FirstName], [LastName]) VALUES (5, N'Janet', N'Doe')
INSERT [dbo].[Contacts] ([ContactId], [FirstName], [LastName]) VALUES (6, N'Chris', N'Doe')
INSERT [dbo].[Contacts] ([ContactId], [FirstName], [LastName]) VALUES (7, N'Russ', N'Doe')
INSERT [dbo].[Lookup] ([Id], [Topic], [PrimaryContactPosition], [SecondaryContactPosition], [ThirdContactPosition]) VALUES (1, N'Topic 1', 1, 2, 3)
INSERT [dbo].[Lookup] ([Id], [Topic], [PrimaryContactPosition], [SecondaryContactPosition], [ThirdContactPosition]) VALUES (2, N'Topic 2', 4, 5, 6)
INSERT [dbo].[Lookup] ([Id], [Topic], [PrimaryContactPosition], [SecondaryContactPosition], [ThirdContactPosition]) VALUES (3, N'Topic 3', 2, 5, 6)
ALTER TABLE [dbo].[ContactPosition]  WITH CHECK ADD  CONSTRAINT [FK_ContactPosition_Contacts] FOREIGN KEY([ContactId])
REFERENCES [dbo].[Contacts] ([ContactId])
GO
ALTER TABLE [dbo].[ContactPosition] CHECK CONSTRAINT [FK_ContactPosition_Contacts]
GO
ALTER TABLE [dbo].[Lookup]  WITH CHECK ADD  CONSTRAINT [FK_Lookup_ContactPosition] FOREIGN KEY([PrimaryContactPosition])
REFERENCES [dbo].[ContactPosition] ([ContactPositionId])
GO
ALTER TABLE [dbo].[Lookup] CHECK CONSTRAINT [FK_Lookup_ContactPosition]
GO
ALTER TABLE [dbo].[Lookup]  WITH CHECK ADD  CONSTRAINT [FK_Lookup_ContactPosition1] FOREIGN KEY([SecondaryContactPosition])
REFERENCES [dbo].[ContactPosition] ([ContactPositionId])
GO
ALTER TABLE [dbo].[Lookup] CHECK CONSTRAINT [FK_Lookup_ContactPosition1]
GO
ALTER TABLE [dbo].[Lookup]  WITH CHECK ADD  CONSTRAINT [FK_Lookup_ContactPosition2] FOREIGN KEY([ThirdContactPosition])
REFERENCES [dbo].[ContactPosition] ([ContactPositionId])
GO
ALTER TABLE [dbo].[Lookup] CHECK CONSTRAINT [FK_Lookup_ContactPosition2]
GO

How can I get the FullName for Secondary and backup also.

SELECT Contacts.ContactId AS ContactId,
       ContactPosition.ContactPositionId,
       ContactPosition.PositionId,
       Lookup.Id,
       Lookup.Topic,
       Lookup.PrimaryContactPosition,
      Contacts.FirstName + ' ' + Contacts.LastName AS PrimaryFullName,
       Lookup.SecondaryContactPosition,
       Lookup.ThirdContactPosition
FROM Contacts
     INNER JOIN ContactPosition ON Contacts.ContactId = ContactPosition.ContactId
     INNER JOIN Lookup ON ContactPosition.ContactPositionId = Lookup.PrimaryContactPosition

Best Answer

Use self join. Contacts is the table that has the FirstName and LastName fields. As you require names of multiple contacts in a single row, use self join on contacts table to get those name.

SELECT c1.ContactId AS ContactId,
           cp.ContactPositionId,
           cp.PositionId,
           l.Id,
           l.Topic,
           l.PrimaryContactPosition,
          c1.FirstName+' '+ c1.LastName AS PrimaryFullName,
           l.SecondaryContactPosition,
           c2.FirstName+' '+ c2.LastName AS SecondaryFullName,
           l.ThirdContactPosition,
           c3.FirstName+' '+ c3.LastName AS ThirdFullName
    FROM Contacts c1
         INNER JOIN ContactPosition cp ON c1.ContactId = cp.ContactId
         INNER JOIN Lookup l ON cp.ContactPositionId = l.PrimaryContactPosition
         INNER JOIN Contacts c2 ON c2.ContactId = l.SecondaryContactPosition
         INNER JOIN Contacts c3 ON c3.ContactId = l.ThirdContactPosition;
Related Question