Мне нужна помощь с составным запросом в MS Access 2010.
У меня есть одна таблица с этой структурой:
ChoirOrder(name, category, day, h_start, h_end, ord);
где name
и category
— первичный ключ ChoirOrder
. Поле category
может быть A
, B
или Y
.
Мне нужно извлечь данные в следующем формате:
FullChoirExecution (
name,
cat_a_day,
cat_a_h_start,
cat_a_h_end,
cat_a_order,
cat_b_day,
cat_b_h_start,
cat_b_h_end,
cat_b_order,
cat_y_day,
cat_y_h_start,
cat_y_h_end,
cat_y_order
);
(каждый хор должен появиться в одной и только одной строке, а name
должен быть первичным ключом).
Проблема в том, что я хочу получить NULL
в полях cat_x_day
, cat_x_h_start
, cat_x_h_end
и cat_x_h_order
(где x
может быть A
, B
или Y
), если в исходной таблице ChoirOrder
у хора нет строк с этой категорией. Например, если данные в ChoirOrder
:
NAME | CAT | DAY | H_START | H_END | ORD
C1 | A | 04/03/2014 | 09:00 | 10:00 | 1
C2 | A | 04/03/2014 | 10:00 | 11:00 | 2
C3 | A | 04/03/2014 | 11:00 | 12:00 | 3
C4 | A | 04/03/2014 | 12:00 | 13:00 | 4
C1 | B | 05/03/2014 | 14:00 | 15:00 | 1
C2 | B | 05/03/2014 | 15:00 | 16:00 | 2
C5 | B | 05/03/2014 | 16:00 | 17:00 | 3
C3 | Y | 06/03/2014 | 09:00 | 10:00 | 1
C5 | Y | 06/03/2014 | 10:00 | 11:00 | 2
C6 | Y | 06/03/2014 | 11:00 | 12:00 | 3
C4 | Y | 06/03/2014 | 12:00 | 13:00 | 4
результат должен быть:
NAME | CAT_A_DAY | CAT_A_H_START | CAT_A_H_END | CAT_A_ORDER | CAT_B_DAY | CAT_B_H_START | CAT_B_H_END | CAT_B_ORDER | CAT_Y_DAY | CAT_Y_H_START | CAT_Y_H_END | CAT_Y_ORDER |
C1 | 04/03/2014 | 09:00 | 10:00 | 1 | 05/03/2014 | 14:00 | 15:00 | 1 | | | | |
C2 | 04/03/2014 | 10:00 | 11:00 | 2 | 05/03/2014 | 15:00 | 16:00 | 2 | | | | |
C3 | 04/03/2014 | 11:00 | 12:00 | 3 | | | | | 06/03/2014 | 09:00 | 10:00 | 1 |
C4 | 04/03/2014 | 12:00 | 13:00 | 4 | | | | | 06/03/2014 | 12:00 | 13:00 | 4 |
C5 | | | | | 05/03/2014 | 16:00 | 17:00 | 3 | 06/03/2014 | 10:00 | 11:00 | 2 |
C6 | | | | | | | | | 06/03/2014 | 11:00 | 12:00 | 3 |
(поскольку у хора C1
нет строки в ChoirOrder
, где категория Y
, поля CAT_Y_DAY
, CAT_Y_H_START
, CAT_Y_H_END
и CAT_Y_ORDER
установлены на NULL
; аналогично для других хоров).
Я подготовил полное внешнее соединение с двумя таблицами со следующим кодом; он работает, извлекая ВСЕ хоры в категории A
и ВСЕ хоры, которые имеют только строку для категории B
(Access 2010 не поддерживает полные внешние соединения, поэтому я использую соединение LEFT join UNION RIGHT join):
SELECT
B.[name] AS [name],
A.[day] AS [cat_a_day],
A.[h_start] AS [cat_a_h_start],
A.[h_end] AS [cat_a_h_end],
A.[ord] AS [cat_a_order],
B.[day] AS [cat_b_day],
B.[h_start] AS [cat_b_h_start],
B.[h_end] AS [cat_b_h_end],
B.[ord] AS [cat_b_order]
FROM
[ChoirOrder] AS A
LEFT OUTER JOIN
[ChoirOrder] AS B
ON
(A.[name] = B.[name])
AND (A.[category]='A' AND B.[category]='B')
UNION
SELECT
B.[name] AS [name],
A.[day] AS [cat_a_day],
A.[h_start] AS [cat_a_h_start],
A.[h_end] AS [cat_a_h_end],
A.[ord] AS [cat_a_order],
B.[day] AS [cat_b_day],
B.[h_start] AS [cat_b_h_start],
B.[h_end] AS [cat_b_h_end],
B.[ord] AS [cat_b_order]
FROM
[ChoirOrder] AS A
RIGHT OUTER JOIN
[ChoirOrder] AS B
ON
(A.[name] = B.[name])
AND (A.[category]='A' AND B.[category]='B')
WHERE A.[category] IS NULL;
Однако этот запрос извлечет только часть предполагаемого результата. Мне нужно объединить его или полностью переписать, чтобы включить также категорию Y
.
Я думал, что могу включить
NULL AS [cat_y_day],
NULL AS [cat_y_h_start],
NULL AS [cat_y_h_end],
NULL AS [cat_y_order]
в запросе, но я не могу заставить его работать. Есть идеи?