Составление сложного SQL запроса

143

02 февраля 2020, 10:20

Использую ms sql. Проект asp.net core. Имеются таблицы БД:

Shipments       ShipmentNomenclatures     Nomenclatures      Products
id              id                        id                 id
                shipment_id               model_id           nomenclature_id
                nomenclature_id           vendorCode         showroom_id

со следующими связями:

Shipment M:M Nomenclature -> Ввели промежуточную таблицу ShipmentNomenclature
Nomenclature 1:M Product

вьюмодель, которую нужно получить:

ShimpentViewModel
Все поля из Shipment
Models
VendorCodes

,где Models и Vendors, конкатенация всех значений полей Model.Name, VendorCode таблиц Nomenclatures, которые связаны с Shimpent.

Первая часть задачи: Необходимо одним sql запросом вывести все ShipmentViewModel

Я смог сделать, вот так:

const string query = @"
    SELECT
        [p].[Id]
        , SUBSTRING(
            (
                SELECT
                    COALESCE(', ' +  [sn.Nomenclature.Model].[Name], '') [text()] 
                FROM   ShipmentNomenclatures AS sn
                INNER JOIN [Nomenclatures] AS [sn.Nomenclature] ON [sn].[NomenclatureId] = [sn.Nomenclature].[Id]
                INNER JOIN [Models] AS[sn.Nomenclature.Model] ON [sn.Nomenclature].[ModelId] = [sn.Nomenclature.Model].[Id]
                WHERE [sn].[ShipmentId] = p.Id
                ORDER BY 1
                FOR XML PATH ('')
            ), 2, 1000) AS [Models]
       , SUBSTRING(
            (
                SELECT
                    COALESCE (', ' + [sn.Nomenclature].[VendorCode], '') [text()]
                FROM   ShipmentNomenclatures AS sn 
                INNER JOIN [Nomenclatures] AS[sn.Nomenclature] ON [sn].[NomenclatureId] = [sn.Nomenclature].[Id]
                WHERE [sn].[ShipmentId] = p.Id
                ORDER BY 1
                FOR XML PATH ('')
            ), 2, 1000) AS [VendorCodes]                                                     
   FROM [Shipments] AS [p]
";

var shipmentViewModels = _dbContext.Value.ShipmentViewModels.FromSql(query);

Вторая часть задачи: вывести все ShipmentViewModel, у которых связанные номенклатуры имеют товары с showroom_Id = 385

С этим у меня проблемы, прошу помочь. Как отфильтровать мой предыдущий запрос по showroom_id?

Answer 1

Например так:

<Ваш запрос>
 where id in(
    select shipment_id
      from ShipmentNomenclatures
      join Nomenclatures on ...
      join Products on ...
     where showroom_id=385
 )