By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[Customer](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](256) NULL,
[LegalName] [nvarchar](256) NULL,
[Notes] [nvarchar](max) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Registry](
[Id] [uniqueidentifier] NOT NULL,
[Alias] [nvarchar](450) NOT NULL,
[Title] [nvarchar](256) NULL,
[Schema] [nvarchar](max) NULL,
[CustomerId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Registry] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
CONSTRAINT [AK_Registry_Alias] UNIQUE NONCLUSTERED
(
[Alias] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[RegistryMember](
[Id] [uniqueidentifier] NOT NULL,
[MemberId] [nvarchar](256) NULL,
[Type] [smallint] NOT NULL,
[Email] [nvarchar](256) NULL,
[DisplayName] [nvarchar](max) NULL,
[Role] [smallint] NOT NULL,
[RegistryId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_RegistryMember] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Request](
[Id] [uniqueidentifier] NOT NULL,
[Status] [smallint] NOT NULL,
[Description] [nvarchar](max) NULL,
[Priority] [smallint] NOT NULL,
[OneilWorkOrderId] [nvarchar](256) NULL,
[ArcManId] [nvarchar](256) NULL,
[ReferenceNumber] [nvarchar](256) NULL,
[Project] [nvarchar](256) NULL,
[DesiredDate] [datetime2](7) NULL,
[AssetType] [smallint] NULL,
[RequestedBy_Id] [nvarchar](256) NULL,
[RequestedBy_Name] [nvarchar](256) NULL,
[RequestedBy_Email] [nvarchar](256) NULL,
[RequestedBy_When] [datetime2](7) NULL,
[AssignedTo_Id] [nvarchar](256) NULL,
[AssignedTo_Name] [nvarchar](256) NULL,
[AssignedTo_Email] [nvarchar](256) NULL,
[AssignedTo_When] [datetime2](7) NULL,
[CompletedBy_Id] [nvarchar](256) NULL,
[CompletedBy_Name] [nvarchar](256) NULL,
[CompletedBy_Email] [nvarchar](256) NULL,
[CompletedBy_When] [datetime2](7) NULL,
[TypeId] [uniqueidentifier] NOT NULL,
[RegistryId] [uniqueidentifier] NOT NULL,
[RecordId] [uniqueidentifier] NULL,
[PublicCheckpointId] [uniqueidentifier] NULL,
[AttachmentId] [uniqueidentifier] NULL,
CONSTRAINT [PK_Request] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Registry] WITH CHECK ADD CONSTRAINT [FK_Registry_Customer_CustomerId] FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
ON DELETE CASCADE
ALTER TABLE [dbo].[Registry] CHECK CONSTRAINT [FK_Registry_Customer_CustomerId]
ALTER TABLE [dbo].[RegistryMember] WITH CHECK ADD CONSTRAINT [FK_RegistryMember_Registry_RegistryId] FOREIGN KEY([RegistryId])
REFERENCES [dbo].[Registry] ([Id])
ON DELETE CASCADE
ALTER TABLE [dbo].[RegistryMember] CHECK CONSTRAINT [FK_RegistryMember_Registry_RegistryId]
ALTER TABLE [dbo].[Request] WITH CHECK ADD CONSTRAINT [FK_Request_Registry_RegistryId] FOREIGN KEY([RegistryId])
REFERENCES [dbo].[Registry] ([Id])
ON DELETE CASCADE
insert into [Customer] values
('9cd5ce29-4f7c-4d61-af8d-d13204b9bba4',
'cust1',
'user1 Petrovski' ,'user1 Petrovski')
1 rows affected
INSERT INTO [Registry]
VALUES
('9cd5ce29-4f7c-4d61-af8d-d13204b8bba4',
'registry 1 alias',
'registry 1',
'schema',
'9cd5ce29-4f7c-4d61-af8d-d13204b9bba4')
1 rows affected
INSERT INTO [RegistryMember] VALUES
('9cd5ce29-4f7c-5d61-af8d-d13204b8bba4'
,NULL
,2
,'email1'
,'member 1'
,2
,'9cd5ce29-4f7c-4d61-af8d-d13204b8bba4')
1 rows affected
INSERT INTO [RegistryMember] VALUES
('9cd5ce29-4f7c-6d61-af8d-d13204b8bba4'
,NULL
,2
,'email2'
,'member 2'
,2
,'9cd5ce29-4f7c-4d61-af8d-d13204b8bba4')
1 rows affected
INSERT INTO [RegistryMember] VALUES
('9cd5ce29-4f7c-7d61-af8d-d13204b8bba4'
,NULL
,2
,'email3'
,'member 3'
,2
,'9cd5ce29-4f7c-4d61-af8d-d13204b8bba4')
1 rows affected
INSERT INTO [Request]
VALUES
('9cd5ce29-4f7c-6d61-af8d-d13204b8bba5'
,2
,'descr'
,2
,NULL
,NULL
,65464351
,NULL
,NULL
,1
,'9cd5ce29-4f7c-5d61-af8d-d13204b8bba4'
,'member 1'
,'email 1'
,'2020-03-19 11:15:01.3317053'
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,'9cd5ce29-4f7c-4d61-af8d-d53204b8bba4'
,'9cd5ce29-4f7c-4d61-af8d-d13204b8bba4'
,'9cd5ce29-4f7c-4d61-af8d-d63204b8bba4'
,'9cd5ce29-4f7c-4d61-af8d-d73204b8bba4'
,'9cd5ce29-4f7c-4d61-af8d-d83204b8bba4')
1 rows affected
INSERT INTO [Request]
VALUES
('9cd5ce29-4f7c-6d61-af8d-d13204b8bca5'
,2
,'descr'
,2
,NULL
,NULL
,65464351
,NULL
,NULL
,1
,'9cd5ce29-4f7c-5d61-af8d-d13204b8bba4'
,'member 1'
,'email 1'
,'2020-03-19 11:15:01.3317053'
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,'9cd5ce29-4f7c-4d61-af8d-d53204b8bba4'
,'9cd5ce29-4f7c-4d61-af8d-d13204b8bba4'
,'9cd5ce29-4f7c-4d61-af8d-d63204b8bba4'
,'9cd5ce29-4f7c-4d61-af8d-d73204b8bba4'
,'9cd5ce29-4f7c-4d61-af8d-d83204b8bba4')
1 rows affected
SELECT UPPER(reg.Title) as TITLE1,
UPPER(cust.[Name]) as CUSTOMER,
Rmember.DisplayName as USERNAME,
Rmember.Email as USEREmail,
COUNT(Request.Id) as REQUESTS
FROM Customer as cust INNER JOIN
Registry as reg ON cust.Id = reg.CustomerId INNER JOIN
RegistryMember as Rmember ON reg.Id = Rmember.RegistryId full JOIN
Request ON reg.Id = Request.RegistryId
group by reg.Title, cust.[Name], cust.LegalName, Rmember.DisplayName,Rmember.Email
order by reg.Title
| TITLE1 | CUSTOMER | USERNAME | USEREmail | REQUESTS |
|---|---|---|---|---|
| REGISTRY 1 | CUST1 | member 1 | email1 | 2 |
| REGISTRY 1 | CUST1 | member 2 | email2 | 2 |
| REGISTRY 1 | CUST1 | member 3 | email3 | 2 |