T-SQL JOIN to display child data in columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL JOIN to display child data in columns

Author

Topic

aitp
Starting Member

4 Posts

Posted - 2010-12-17 : 01:51:03

I have a query result that displays as

ID | First | Last | Agent | Examiner
369 | Daniel | Barlow | Ginger Orozco | Ginger Orozco
369 | Daniel | Barlow | Ava Holley | Ava Holley

But I want it to display on a single row as

ID | Firstname | Lastname | Agent | Examiner
369 | Daniel | Barlow | Ginger Orozco | Ava Holley

How do I do this?

My query is (wrong, but listed below)

SELECT c.ClientID, c.firstName, c.lastname, u.firstname + ' ' + u.lastname as Agent,
u.firstname + ' ' + u.lastname as Examiner
FROM Clients c
JOIN ClientUsers cu on (c.clientID = cu.clientID)
JOIN Users u on (cu.userID = u.userID)
JOIN UserRoles ur on (u.userID = ur.userID)
JOIN Roles r on (ur.roleID = r.roleID)
WHERE 1=1 AND c.Active=1
ORDER BY c.lastname, c.firstname;

USE [DB]
GO
/****** Object: Table [dbo].[Clients] Script Date: 12/16/2010 23:46:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Clients](
[clientID] [int] IDENTITY(1,1) NOT NULL,
[Gender] [nchar](10) NOT NULL,
[FirstName] [nchar](25) NOT NULL,
[MiddleName] [nchar](25) NULL,
[LastName] [nchar](25) NOT NULL,
[BirthDate] [datetime] NOT NULL,
[SSN] [nvarchar](11) NOT NULL,
[HomeAddress1] [nchar](50) NULL,
[HomeAddress2] [nchar](50) NULL,
[HomeAddressCity] [nchar](50) NOT NULL,
[HomeAddressState] [nchar](25) NOT NULL,
[HomeAddressPostalCode] [nchar](25) NULL,
[MailingAddress1] [nchar](50) NULL,
[MailingAddress2] [nchar](50) NULL,
[MailingAddressCity] [nchar](50) NULL,
[MailingAddressState] [nchar](25) NULL,
[MailingAddressPostalCode] [nchar](25) NULL,
[WorkAddress1] [nchar](50) NULL,
[WorkAddress2] [nchar](50) NULL,
[WorkAddressCity] [nchar](50) NULL,
[WorkAddressState] [nchar](25) NULL,
[WorkAddressPostalCode] [nchar](25) NULL,
[Phone1] [nchar](50) NULL,
[Phone1Type] [nchar](20) NULL,
[Phone2] [nchar](50) NULL,
[Phone2Type] [nchar](20) NULL,
[Phone3] [nchar](50) NULL,
[Phone3Type] [nchar](20) NULL,
[EmailAddress] [nchar](50) NULL,
[Smoker] [nchar](1) NOT NULL,
[InsuranceCompany] [nvarchar](50) NOT NULL,
[AgentNumber] [nchar](50) NULL,
[AgencyID] [nchar](50) NULL,
[PolicyType] [nchar](50) NULL,
[PolicyAmount] [money] NOT NULL,
[ServicesCompletedDateTime] [datetime] NULL,
[Status] [nchar](50) NOT NULL,
[StatusDateTime] [datetime] NOT NULL,
[Notes] [text] NULL,
[Active] [bit] NOT NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[clientID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[Clients] WITH CHECK ADD CONSTRAINT [FK_Client_Policies] FOREIGN KEY([PolicyType])
REFERENCES [dbo].[Policies] ([PolicyType])
GO
ALTER TABLE [dbo].[Clients] CHECK CONSTRAINT [FK_Client_Policies]
GO
ALTER TABLE [dbo].[Clients] WITH CHECK ADD CONSTRAINT [FK_Client_Status] FOREIGN KEY([Status])
REFERENCES [dbo].[Status] ([Status])
GO
ALTER TABLE [dbo].[Clients] CHECK CONSTRAINT [FK_Client_Status]

USE [DB]
GO
/****** Object: Table [dbo].[ClientUsers] Script Date: 12/16/2010 23:47:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ClientUsers](
[clientID] [int] NOT NULL,
[userID] [int] NOT NULL,
CONSTRAINT [PK_ClientUsers] PRIMARY KEY CLUSTERED
(
[clientID] ASC,
[userID] 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
ALTER TABLE [dbo].[ClientUsers] WITH CHECK ADD CONSTRAINT [FK_ClientUsers_Client] FOREIGN KEY([clientID])
REFERENCES [dbo].[Clients] ([clientID])
GO
ALTER TABLE [dbo].[ClientUsers] CHECK CONSTRAINT [FK_ClientUsers_Client]
GO
ALTER TABLE [dbo].[ClientUsers] WITH CHECK ADD CONSTRAINT [FK_ClientUsers_Users] FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([userID])
GO
ALTER TABLE [dbo].[ClientUsers] CHECK CONSTRAINT [FK_ClientUsers_Users]

USE [DB]
GO
/****** Object: Table [dbo].[Roles] Script Date: 12/16/2010 23:47:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
[roleID] [int] IDENTITY(1,1) NOT NULL,
[Role] [nvarchar](50) NOT NULL,
[SortOrder] [int] NOT NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[roleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

USE [DB]
GO
/****** Object: Table [dbo].[UserRoles] Script Date: 12/16/2010 23:48:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
[userID] [int] NOT NULL,
[roleID] [int] NOT NULL,
CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
[userID] ASC,
[roleID] 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
ALTER TABLE [dbo].[UserRoles] WITH CHECK ADD CONSTRAINT [FK_UserRoles_Roles] FOREIGN KEY([roleID])
REFERENCES [dbo].[Roles] ([roleID])
GO
ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Roles]
GO
ALTER TABLE [dbo].[UserRoles] WITH CHECK ADD CONSTRAINT [FK_UserRoles_Users] FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([userID])
GO
ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Users]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-17 : 12:14:09

So a User is both an Angent and an Examiner at the same time for a Client? How can you tell which User is supposed to be the Agent and who is the Examiner? Does the UserRole play a part in that?

It might help if you posted some sample data that we can query against.

aitp
Starting Member

4 Posts

Posted - 2010-12-20 : 12:17:23

In the application currently a user can be an administrator, agent, or examiner, but can hold only one role. Currently a client can have up to 1 agent and 1 examiner assigned.

This should set up default roles, create two users, and assign the first as an Agent, and the second as an Examiner. The role is assigned at new user registration, and they can only select one type.

--create roles

insert into roles (Role, SortOrder) values ('SYSTEM' 1)
insert into roles (Role, SortOrder) values ('Administrators' 2)
insert into roles (Role, SortOrder) values ('Agents' 4)
insert into roles (Role, SortOrder) values ('Examiners' 5)

--create two users, an agent and an examiner

INSERT INTO Users
([emailAddress]
,[password]
,[FirstName]
,[Lastname]
,[SSN]
,[CompanyAgencyName]
,[WorkAddress1]
,[WorkAddressCity]
,[WorkAddressState]
,[WorkAddressPostalCode]
,[Phone1]
,[Phone1Type])
VALUES
('me@myisp.com'
,'test'
,'Joe'
,'Agent'
,'555-55-5555'
,'Test Company'
,'1234 Test Road'
,'Logan'
,'UT'
,'84321'
,'4357552000'
,'Office')

INSERT INTO Users
([emailAddress]
,[password]
,[FirstName]
,[Lastname]
,[SSN]
,[CompanyAgencyName]
,[WorkAddress1]
,[WorkAddressCity]
,[WorkAddressState]
,[WorkAddressPostalCode]
,[Phone1]
,[Phone1Type])
VALUES
('you@yourisp.com'
,'test'
,'Larry'
,'Examiner'
,'555-44-5555'
,'Another Test Company'
,'4321 Test Road'
,'Logan'
,'UT'
,'84321'
,'4357552001'
,'Cell')

--assign user to role

insert into UserRoles (userID, roleID) VALUES (PK_USER1, 3) -- User1 is an Agent
insert into UserRoles (userID, roleID) VALUES (PK_USER2, 4) -- User2 is an Examiner

--create client

INSERT INTO [Clients]
([Gender]
,[FirstName]
,[LastName]
,[BirthDate]
,[SSN]
,[HomeAddressCity]
,[HomeAddressState]
,[Smoker]
,[InsuranceCompany]
,[PolicyAmount]
,[Status]
,[StatusDateTime]
,[Active])
VALUES
('M'
,'Roger'
,'Client'
,'11/11/1911'
,'444-55-4444'
,'Bountiful'
,'UT'
,'N'
,'Test Company'
,'15000'
,'New Client'
,'12/20/2010 00:00:00.000'
,'Y')

-- Assign the Agent and Examiner to a Client

INSERT INTO [ClientUsers]
([clientID]
,[userID])
VALUES
(1,1)

INSERT INTO [ClientUsers]
([clientID]
,[userID])
VALUES
(1,2)

aitp
Starting Member

4 Posts

Posted - 2010-12-21 : 12:34:13

any thoughts?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources