Структура
USE [test]
GO
/****** Object: Table [dbo].[Galleries] Script Date: 1/15/2022 11:17:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Galleries](
[id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Galleries] 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]
GO
/****** Object: Table [dbo].[Images] Script Date: 1/15/2022 11:17:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Images](
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Taken] [datetime] NOT NULL,
[Path] [nvarchar](400) NOT NULL,
[ParentId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Images] 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]
GO
/****** Object: Table [dbo].[Users] Script Date: 1/15/2022 11:17:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[Id] [uniqueidentifier] NOT NULL,
[FirstName] [nvarchar](200) NULL,
[LastName] [nvarchar](200) NULL,
[Burth] [datetime] NULL,
[Sex] [tinyint] NULL,
[Email] [nvarchar](200) NOT NULL,
CONSTRAINT [PK_Users] 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]
GO
ALTER TABLE [dbo].[Galleries] ADD CONSTRAINT [DF_Galleries_id] DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[Images] ADD CONSTRAINT [DF_Images_Id] DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_Id] DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[Galleries] WITH CHECK ADD CONSTRAINT [FK_Galleries_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([Id])
GO
ALTER TABLE [dbo].[Galleries] CHECK CONSTRAINT [FK_Galleries_Users]
GO
Выборки
SELECT TOP 1000 u.Id
, FirstName
, LastName
, Burth
, Sex
, Email ,
i.Name as ImageName,
i.Path as ImagePath
FROM Users as u
LEFT JOIN Images i on u.id = i.ParentId
GO
SELECT TOP 1000 g.id
,Name
,UserId,
u.firstName + ' ' + u.LastName as UserName
FROM Galleries g
LEFT JOIN users u on g.UserId = u.id
GO
SELECT TOP 1000 i.Id
, i.Name
, Taken
, Path
, ParentId
, g.Name as GaleryName
, CASE WHEN g.Name IS NOT NULL THEN 'galery' ELSE 'avatar' END as ImageType
FROM Images i
LEFT JOIN Galleries g on i.ParentId = g.id
order by ParentId
GO
