[sql, ms reporting] подскажите по группироке в запросе, пожалуйста

leorita04

товарищи, добрый день!
есть у меня желание написать запрос, но вот никак не пойму, как это сделать
может быть у кого есть идеи?
суть:
Имеются следующие таблицы:
 

Квартира

Холодильник

Коробка

Продукт_коробка

Пакет

Продукт_пакет 

Связи:
С одной квартире может быть N холодильников (1:N)
В одном холодильнике может быть N коробок (1:N)
В одном холодильнике может быть N пакетов (1:N)
В одной коробке может быть N продуктов с различным количеством, эти же продукты могут быть в другой коробке (N:N)
В одном пакете может быть N продуктов с различным количеством, эти же продукты могут быть в другом пакете (N:N)

Что я пытаюсь получить:
таблицу, в которой суммировалось бы отдельно количество по продуктам в коробках и пакетах
что-то вроде того (пытаюсь получить оба варианта):

Пишу в Visual Studio 2008 Reporting Services, группирую уже на форме отчета
Был такой вариант запроса, но он, очевидно, не верен:
 

SELECT     *

FROM         Квартира INNER JOIN

                Холодильник ON Квартира.Квартира_id = Холодильник.Квартира_id INNER JOIN

                Коробка ON Коробка.Холодильник_id = Холодильник.Холодильник_id INNER JOIN

                Продукт_коробка ON Коробка.Коробка_id = Продукт_коробка.Коробка_id FULL OUTER JOIN

                Продукт_пакет ON Продукт_пакет.Продукт_id = Продукт_коробка.Продукт_id INNER JOIN

                Пакет ON Пакет.Пакет_id = Продукт_пакет.Пакет_id AND 

                Холодильник.Холодильник_id = Пакет.Холодильник_id 

Dasar

что-то такое
select *
from Квартира
inner join Холодильник on Квартира.Квартира_Id = Холодильник.Квартира_Id
inner join
(
select Пакет_id as П_Id, Продукт_Id, Количество, 'Пакет' as kind
from Пакет
inner join Продукт_Пакет
on Пакет.Пакет_Id = Продукт_Пакет.Пакет_Id
union
select Коробка_id as П_Id, Продукт_Id, Количество, 'Коробка' as kind
from Коробка
inner join Продукт_Коробка
on Коробка.Коробка_Id = Продукт_Коробка.Коробка_Id
) as П
on Холодильник.Холодильник_Id = П.Холодильник_Id

0000

есть у меня желание написать запрос, но вот никак не пойму, как это сделать

1. Молча
2. Постепенно: сначала вывести квартиры, потом квартиры и холодильники, потом добавить ящики и т.д.

leorita04

, спасибо!
почти получилось.
единственная загвоздка осталась вот в чем - при группировке по продуктам появляются пустые ячейки, если продукт есть в одном холодильнике и в коробках и в пакетах:

так как, видимо, Продукт_Коробка и Продукт_Пакет - разные таблицы
я попытался связать их с таблицей Продукт по Продукт_id, вот так:

select *

from Квартира

inner join Холодильник on Квартира.Квартира_Id = Холодильник.Квартира_Id

inner join 

(

  select Пакет_id as П_Id, Продукт.Продукт_Id as Прод, Количество, Пакет.Холодильник_id as Холод_id, 'Пакет' as kind

  from Пакет

  inner join Продукт_Пакет 

  on Пакет.Пакет_Id = Продукт_Пакет.Пакет_Id 

  inner join Продукт on Продукт.Продукт_id = Продукт_Пакет.Продукт_id

  union

  select Коробка_id as П_Id, Продукт.Продукт_Id as Прод, Количество, Коробка.Холодильник_id as Холод_id, 'Коробка' as kind

  from Коробка

  inner join Продукт_Коробка 

  on Коробка.Коробка_Id = Продукт_Коробка.Коробка_Id

  inner join Продукт on Продукт.Продукт_id = Продукт_Коробка.Продукт_id

) as П 

on Холодильник.Холодильник_Id = П.Холод_Id  

но Visual Studio переделывает таблицу Продукт ниже union в Продукт_1 и считает ее другой таблицей, судя по всему:

select *

from Квартира

inner join Холодильник on Квартира.Квартира_Id = Холодильник.Квартира_Id

inner join 

(

  select Пакет_id as П_Id, Продукт.Продукт_Id as Прод, Количество, Пакет.Холодильник_id as Холод_id, 'Пакет' as kind

  from Пакет

  inner join Продукт_Пакет 

  on Пакет.Пакет_Id = Продукт_Пакет.Пакет_Id 

  inner join Продукт on Продукт.Продукт_id = Продукт_Пакет.Продукт_id

  union

  select Коробка_id as П_Id, Продукт_1.Продукт_Id as Прод, Количество, Коробка.Холодильник_id as Холод_id, 'Коробка' as kind

  from Коробка

  inner join Продукт_Коробка 

  on Коробка.Коробка_Id = Продукт_Коробка.Коробка_Id

  inner join Продукт_1 on Продукт_1.Продукт_id = Продукт_Коробка.Продукт_id

) as П 

on Холодильник.Холодильник_Id = П.Холод_Id  

почему так?

Dasar

при группировке по продуктам появляются пустые ячейки

как группируешь?
> union в Продукт_1 и считает ее другой таблицей
во-первых, это не должно влиять
во-вторых, зачем тебя связка с Продукт, если ты в запросе не используешь данные из нее
в-третьих, связку с Продукт можно вынести из под скобок, т.к. оба варианта: и пакеты, и коробки приведены к одинаковому виду

) as П 

on Холодильник.Холодильник_Id = П.Холод_Id 

inner join Продукт on П.Продукт_Id = Продукт.Продукт_Id

leorita04

группирую вот так:

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

Dasar

ячейку кол-во тоже замени на сумму

leorita04

заменил, тот же результат (бонус пустые ячейки)

Dasar

тогда надо смотреть логику работы report generator-а
если убрать П_Id из внутренних select-ов что-нибудь меняется?
select Продукт_Id, Количество, 'Пакет' as kind

leorita04

меняется, но не сказать, чтобы существенно)

Dasar

у тебя там стоит группировка по полю количеству?

leorita04

да. и по сумме пробовал также

Dasar

да. и по сумме пробовал также

убери из этой ячейки группировку, оставь просто сумму

leorita04

о, один из вариантов получен

спасибо большое)
буду пробовать теперь первый вариант получить.

Slaider

гэг зэбрэз, дэздэвлэн?