Здравствуйте, kRaB, Вы писали:

Вот наваял. Правда пришлось временную таблицу использовать. Если кто из гуру поправит будет хорошо

USE [master]
GO
DROP DATABASE [test]
GO
CREATE DATABASE [test] COLLATE Cyrillic_General_CI_AS
GO
USE [test]
GO
-- ТИПЫ ДЕТАЛЕЙ
CREATE TABLE [test].[dbo].[unit_types]
 (
  [type_id] INT PRIMARY KEY CLUSTERED NOT NULL,
  [name] NVARCHAR(32) NOT NULL
 )
GO
-- ДЕТАЛИ
CREATE TABLE [test].[dbo].[units]
 (
  [unit_id] INT PRIMARY KEY CLUSTERED NOT NULL,
  [type_id] INT FOREIGN KEY REFERENCES [unit_types]([type_id]) ON DELETE CASCADE ON UPDATE CASCADE,
  [name] NVARCHAR(32) NOT NULL
 )
GO
-- КОНТЕЙНЕРЫ
CREATE TABLE [test].[dbo].[containers]
 (
  [con_id] INT PRIMARY KEY CLUSTERED NOT NULL,
  [name] NVARCHAR(32) NOT NULL
 )
GO
-- РАСПРЕДЕЛЕНИЕ ДЕТАЛЕЙ ПО КОНТЕЙНЕРАМ
CREATE TABLE [test].[dbo].[units_in_containers]
 (
  [unit_id] INT FOREIGN KEY REFERENCES [units]([unit_id]) ON DELETE CASCADE ON UPDATE CASCADE,
  [con_id] INT FOREIGN KEY REFERENCES [containers]([con_id]) ON DELETE CASCADE ON UPDATE CASCADE
 )
GO
-- СОЗДАЁМ ТИПЫ
INSERT INTO [unit_types] ([type_id], [name]) VALUES (1, 'TYPE_A')
INSERT INTO [unit_types] ([type_id], [name]) VALUES (2, 'TYPE_B')
INSERT INTO [unit_types] ([type_id], [name]) VALUES (3, 'TYPE_C')
-- СОЗДАЁМ ДЕТАЛИ
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (1, 1, 'UNIT_I')
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (2, 1, 'UNIT_II')
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (3, 1, 'UNIT_III')
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (4, 2, 'UNIT_IV')
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (5, 2, 'UNIT_V')
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (6, 2, 'UNIT_VI')
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (7, 3, 'UNIT_VII')
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (8, 3, 'UNIT_VIII')
INSERT INTO [units] ([unit_id], [type_id], [name]) VALUES (9, 3, 'UNIT_IX')
-- СОЗДАЁМ КОНТЕЙНЕРЫ
INSERT INTO [containers] ([con_id], [name]) VALUES (1, 'CON_1')
INSERT INTO [containers] ([con_id], [name]) VALUES (2, 'CON_2')
INSERT INTO [containers] ([con_id], [name]) VALUES (3, 'CON_3')
-- РАСПИХИВАЕМ ДЕТАВЛИ ПО КОНТЕЙНЕРАМ
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(1,1)
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(2,1)
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(3,2)
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(4,1)
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(5,2)
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(6,3)
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(7,3)
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(8,2)
INSERT INTO [units_in_containers] ([unit_id], [con_id]) VALUES(9,3)
-- НАЧИНАЕМ ПОЛУЧАТЬ ИНФОРМАЦИЮ
-- Все вхождения деталей в контейнеры
SELECT * FROM [units_in_containers]
-- Все детали в контейнере 1
SELECT [units].[unit_id], [units].[type_id]
FROM [units]
INNER JOIN [units_in_containers] ON [units_in_containers].[unit_id] = [units].[unit_id]
WHERE [units_in_containers].[con_id] = 1
-- Количество деталей кадого типа входящего в контейнер 1, еслит типа в контейнере нет, то он не показывается
SELECT [units].[type_id], COUNT([units].[unit_id]) AS [type_count]
FROM [units]
INNER JOIN [units_in_containers] ON [units_in_containers].[unit_id] = [units].[unit_id]
WHERE [units_in_containers].[con_id] = 1
GROUP BY [units].[type_id]
-- А ТЕПЕРЬ ТО ЧТО НАДО
-- Получаем только существующие типы
SELECT [unit_types].[type_id], COUNT([units].[unit_id]) AS [type_count]
INTO [#temp]
FROM [unit_types]
FULL JOIN [units] ON [unit_types].[type_id] = [units].[type_id]
FULL JOIN [units_in_containers] ON [units_in_containers].[unit_id] = [units].[unit_id]
WHERE [units_in_containers].[con_id] = 1
GROUP BY [unit_types].[type_id]
-- Выдаём все. Где количество равно нулю - будет NULL
SELECT [unit_types].[type_id], [#temp].[type_count] AS [type_count]
FROM [unit_types]
FULL JOIN [#temp] ON [#temp].[type_id] = [unit_types].[type_id]
--
DROP TABLE [#temp]
--