Join and flatten table output

I am currently trying to join 3 tables. The main table is company, if there is 3 companies and 2 roles exists in the role table I want the output to be like this:

Company1, Role 1, <iduser>
Company1, Role 2, <iduser>
Company2, Role 1, <iduser>
Company2, Role 2, <iduser>
Company3, Role 1, <iduser>
Company3, Role 2, <iduser>

For each role there must be a row for a company. If no user is linked to a role and company then it should just show: Companyname, Rolename, null

Database schema:

CREATE TABLE [dbo].[role](
    [idrole] [int] IDENTITY(1,1) NOT NULL,
    [name] [nchar](100) NOT NULL,
 CONSTRAINT [PK_role] PRIMARY KEY CLUSTERED 
(
    [idrole] 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].[company](
    [idcompany] [int] IDENTITY(1,1) NOT NULL,
    [name] [nchar](100) NOT NULL,
 CONSTRAINT [PK_company] PRIMARY KEY CLUSTERED 
(
    [idcompany] 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].[user](
    [iduser] [int] IDENTITY(1,1) NOT NULL,
    [name] [nchar](100) NOT NULL,
    [idcompany] [int] NOT NULL,
    [idrole] [int] NULL,
 CONSTRAINT [PK_user] PRIMARY KEY CLUSTERED 
(
    [iduser] 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].[user]  WITH CHECK ADD  CONSTRAINT [FK_user_company] FOREIGN KEY([idcompany])
REFERENCES [dbo].[company] ([idcompany])
GO

ALTER TABLE [dbo].[user] CHECK CONSTRAINT [FK_user_company]
GO

ALTER TABLE [dbo].[user]  WITH CHECK ADD  CONSTRAINT [FK_user_role] FOREIGN KEY([idrole])
REFERENCES [dbo].[role] ([idrole])
GO

ALTER TABLE [dbo].[user] CHECK CONSTRAINT [FK_user_role]
GO

Test data script:

INSERT INTO [test].[dbo].[company]
VALUES ('Company 1')

INSERT INTO [test].[dbo].[company]
VALUES ('Company 2')

INSERT INTO [test].[dbo].[company]
VALUES ('Company 3')


INSERT INTO [test].[dbo].[role]
VALUES ('Role 1')

INSERT INTO [test].[dbo].[role]
VALUES ('Role 2')

Attempt:

SELECT roles.name, users.iduser 
FROM [test].[dbo].[role] roles
JOIN [test].[dbo].[user] users ON roles.idrole = users.idrole
JOIN [test].[dbo].[company] company ON company.idcompany = users.idcompany

Please advise what approach I can use to achieve this

Answer

Given you don’t have a relationship between a Role and a Company, I assume you want every combination of Role/Company, which is where you use a CROSS JOIN.

Then you would LEFT JOIN your User table on since you want to return null if a user doesn’t exist for a given Company-Role.

SELECT C.[name], R.[name], U.iduser 
FROM [test].[dbo].[role] R
CROSS JOIN [test].[dbo].[company] C
LEFT JOIN [test].[dbo].[user] U ON U.idrole = R.idrole and U.idcompany = C.idcompany
ORDER BY C.[name], R.[name], U.iduser;

Note the use of shorter aliases for clarity.

Advertisement

Answer