Блог

Главная - Вопросы по программированию - Использование таблиц промежуточного соединения в сложном запросе SQL

Использование таблиц промежуточного соединения в сложном запросе SQL

#sql #join #sql-server-2019

#sql #Присоединиться #sql-сервер-2019

Вопрос:

Я унаследовал проект от делового партнера и пытаюсь добавить к нему, но здесь я бегу против ветра. Это коктейльная база данных, состоящая из 7 таблиц.

 drinks (ID, drinkName, lu_category, lu_glassware, lu_IBA)
category (categoryID, category)
glassware (glasswareID, glassware)
ingredients (ingredientID, ingredient, notes)
measure (measureID, measure)
IBA (IBAID, IBA)
ingredient_drinks_measure (idmID, drinkID, ingredientID, measureID)

Этот запрос работает как ожидалось и возвращает все в одну строку для каждого напитка.

 SELECT        dbo.drinks.id AS [drinkID], 
              dbo.drinks.drinkName, 
              dbo.category.category, 
              dbo.glassware.glassware, 
              dbo.IBA.IBA, 
              string_agg(measure.measure   ' '   ingredients.ingredient, ', ') as [Ingredients]
FROM          dbo.glassware 
              RIGHT OUTER JOIN dbo.measure 
              LEFT OUTER JOIN dbo.ingredient_drinks_measure ON dbo.measure.measureID = dbo.ingredient_drinks_measure.measureID 
              RIGHT OUTER JOIN dbo.ingredients ON dbo.ingredient_drinks_measure.ingredientID = dbo.ingredients.ingredientID 
              RIGHT OUTER JOIN dbo.drinks ON dbo.ingredient_drinks_measure.drinkID = dbo.drinks.id 
              LEFT OUTER JOIN dbo.IBA ON dbo.drinks.lu_IBA = dbo.IBA.IBAID ON dbo.glassware.glasswareID = dbo.drinks.lu_glassware 
              LEFT OUTER JOIN dbo.category ON dbo.drinks.lu_category = dbo.category.categoryID
GROUP BY      dbo.drinks.id, 
              dbo.drinks.drinkName, 
              dbo.drinks.imagePath, 
              dbo.drinks.dateModified, 
              dbo.category.category, 
              dbo.glassware.glassware, 
              dbo.IBA.IBA

ВОЗВРАТ

 drinkID | drinkName | category  | glassware         | IBA                   | ingredients
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar

Теперь мне нужно добавить ТЕГИ к каждому напитку
На данный момент существует только 22 тега, и с каждым напитком может быть связано от 1 до 22 тегов (теги типа «Хэллоуин», «Рождество», «Чаша для пунша», «Завтрак», «Званый ужин», «Фруктовый», «Пьяный» и т.д.).

У меня есть tags table (id, tag) и у меня есть drinkTags table (drinkID, tagID) , сидящий посередине, чтобы объединить напитки и теги вместе (сценарии СОЗДАНИЯ для обоих приведены ниже).

Однако, как бы я ни старался, когда я добавляю объединения к запросу и сопутствующий столбец string_agg для ТЕГОВ (разделенный запятыми сводный столбец), по крайней мере, один из столбцов string_agg дублируется и / или я получаю несколько строк для каждого напитка. например, это:

 drinkID | drinkName | category  | glassware         | IBA                   | ingredients
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar, 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar, 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar

или это:

 drinkID | drinkName | category  | glassware         | IBA                   | tags      | ingredients
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | Alcoholic | 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | IBA       | 2 tsp Superfine Sugar, Juice of 1 Lime, 2-3 oz Jamaican Rum
11000   | Mojito    | Cocktail  | Highball glass    | Contemporary Classics | USA       | 2-3 oz Jamaican Rum, Juice of 1 Lime, 2 tsp Superfine Sugar

Есть мысли? Я был бы признателен за любую помощь, даже если это просто указывает мне на то, что я упустил из виду во всех своих исследованиях.

Вот сценарии СОЗДАНИЯ, если они вам нужны.

 /****** Object:  Table [dbo].[drinks]    Script Date: 9/15/2020 1:25:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[drinks](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [drinkName] [varchar](37) NOT NULL,
    [lu_category] [int] NULL,
    [lu_IBA] [int] NULL,
    [lu_glassware] [int] NULL
 CONSTRAINT [PK__drinks_c__2B658F5CD9E4315A] 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].[category]    Script Date: 9/15/2020 1:27:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[category](
    [categoryID] [int] IDENTITY(1,1) NOT NULL,
    [category] [nvarchar](50) NULL,
 CONSTRAINT [PK__category__23CAF1F80317C8FA] PRIMARY KEY CLUSTERED 
(
    [categoryID] 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].[drinkTags]    Script Date: 9/15/2020 1:27:54 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[drinkTags](
    [drinkID] [int] NOT NULL,
    [tagID] [int] NOT NULL,
 CONSTRAINT [PK_drinkTags_1] PRIMARY KEY CLUSTERED 
(
    [drinkID] ASC,
    [tagID] 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].[glassware]    Script Date: 9/15/2020 1:28:08 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[glassware](
    [glasswareID] [int] IDENTITY(1,1) NOT NULL,
    [glassware] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [glasswareID] 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].[IBA]    Script Date: 9/15/2020 1:28:19 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[IBA](
    [IBAID] [int] IDENTITY(1,1) NOT NULL,
    [IBA] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [IBAID] 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].[ingredient_drinks_measure]    Script Date: 9/15/2020 1:28:32 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ingredient_drinks_measure](
    [idmID] [int] IDENTITY(1,1) NOT NULL,
    [drinkID] [int] NULL,
    [ingredientID] [int] NULL,
    [measureID] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [idmID] 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].[ingredients]    Script Date: 9/15/2020 1:28:44 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ingredients](
    [ingredientID] [int] IDENTITY(1,1) NOT NULL,
    [ingredient] [nvarchar](150) NULL
PRIMARY KEY CLUSTERED 
(
    [ingredientID] 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]
GO



/****** Object:  Table [dbo].[measure]    Script Date: 9/15/2020 1:29:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[measure](
    [measureID] [int] IDENTITY(1,1) NOT NULL,
    [measure] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED 
(
    [measureID] 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].[tags]    Script Date: 9/15/2020 1:29:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tags](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tag] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_drinkTags] 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

Ответ №1:

Решение 1:

Имеет ли напиток однозначную связь с тегом? Если это так, то все, что вам нужно сделать, это добавить столбец тегов в таблицу напитков. Это будет ненормализованный способ решения проблемы.

Решение 2:

Если у drink нет однозначной связи с объектом tag.
В какой таблице хранится взаимосвязь между напитками и тегами? Если у вас нет этой таблицы, вам нужно создать ее и заполнить данными. Если вам нужна помощь в создании отношения drinks к тегу, я могу помочь вам с этим. Затем вам нужно будет обновить SQL, чтобы использовать отношение tag to drink для получения единой записи, а также добавить поле tag в SQL.

Комментарии:

1. У каждого напитка может быть много тегов, поэтому это определенно не отношение 1 к 1. Существует таблица тегов, в которой хранятся идентификатор тега и имя тега, также существует таблица, которую я назвал drinkTags, в которой хранятся идентификатор drinkID и идентификатор TagID. Итак, в продолжение примеров, которые я привел в своем посте, таблица drinkTags будет содержать 3 записи для drinkID # 11000. Это объединение этих двух таблиц и добавление столбца string_agg, который, кажется, дает мне подгонку.

Ответ №2:

Надеюсь, приведенный ниже запрос решит вашу проблему. Я добавил одно поле string_agg, чтобы возвращать все теги, связанные с напитком, и два ВНУТРЕННИХ СОЕДИНЕНИЯ.

 SELECT        dbo.drinks.id AS [drinkID], 
              dbo.drinks.drinkName, 
              dbo.category.category, 
              dbo.glassware.glassware, 
              dbo.IBA.IBA, 
              string_agg(measure.measure   ' '   ingredients.ingredient, ', ') as [Ingredients],
          string_agg(dbo.tags.tag, ', ‘) as [Tags]
FROM          dbo.glassware 
              RIGHT OUTER JOIN dbo.measure 
              LEFT OUTER JOIN dbo.ingredient_drinks_measure ON dbo.measure.measureID = dbo.ingredient_drinks_measure.measureID 
              RIGHT OUTER JOIN dbo.ingredients ON dbo.ingredient_drinks_measure.ingredientID = dbo.ingredients.ingredientID 
              RIGHT OUTER JOIN dbo.drinks ON dbo.ingredient_drinks_measure.drinkID = dbo.drinks.id 
              LEFT OUTER JOIN dbo.IBA ON dbo.drinks.lu_IBA = dbo.IBA.IBAID ON dbo.glassware.glasswareID = dbo.drinks.lu_glassware 
              LEFT OUTER JOIN dbo.category ON dbo.drinks.lu_category = dbo.category.categoryID
          INNER JOIN  dbo.drinkTags.drinkID =  dbo.drinks.id    
          INNER JOIN  dbo.tags.id = dbo.drinkTags.tagID
GROUP BY      dbo.drinks.id, 
              dbo.drinks.drinkName, 
              dbo.drinks.imagePath, 
              dbo.drinks.dateModified, 
              dbo.category.category, 
              dbo.glassware.glassware, 
              dbo.IBA.IBA

Комментарии:

1. Большое вам спасибо, но, боюсь, это очень похоже на результаты, которые я получаю. В этом случае столбец Ingredients и столбец Tags дублировались внутри string_agg, равного количеству элементов, которые необходимо объединить: например, в столбце, разделенном запятыми, три тега (Alcohol, IBA, USA) повторяются три раза. То же самое верно для 3 ингредиентов (ром, лайм, сахар), которые повторяются 3 раза в столбце, разделенном запятыми.

Ответ №3:

После гораздо большего исследования я нашел решение, которое будет работать для меня. Это делает предположение, что для каждого drinkID есть хотя бы один тег, но на данный момент я могу с этим справиться.

 ;WITH a AS
(
SELECT 
    d.id,
    d.drinkName as [Name] ,
    c.category as [Category],
    g.glassware as [Glass],
    b.IBA as [IBA],
    string_agg(m.measure   ' '   i.ingredient, ', ') as [Ingredients],
    d.instructions as [Instructions],
    string_agg(i.notes, ', ') as [IngredientNotes],
    d.addedInfo as [Additional Information],
    d.imagePath as [Image]
FROM drinks d
    JOIN category c on c.categoryID = d.lu_category
    JOIN glassware g on g.glasswareID = d.lu_glassware
    LEFT JOIN IBA b on b.IBAID = d.lu_IBA
    JOIN ingredient_drinks_measure idm on idm.drinkID = d.id
    JOIN ingredients i on i.ingredientID = idm.ingredientID
    JOIN measure m on m.measureID = idm.measureID
group by d.id, d.drinkName, c.category, g.glassware, b.IBA, d.addedInfo, d.imagePath, d.instructions
)
SELECT a.*, dtm.tags
FROM a
JOIN (select 
        drinkID, string_agg(t.tag, ', ') as tags
        from tags t
        join tagmap tm on tm.tagID = t.id
        group by drinkID) as dtm
ON a.ID = dtm.drinkID