SQL Access 2010 — полное внешнее соединение трех таблиц

Мне нужна помощь с составным запросом в 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]

в запросе, но я не могу заставить его работать. Есть идеи?


person jiraky90    schedule 04.03.2014    source источник


Ответы (1)


Следующий перекрестный запрос, кажется, дает желаемый результат:

TRANSFORM First(field_value) AS v
SELECT [name]
FROM
    (
        SELECT
            [name],
            'cat_' & LCase([cat]) & '_day' AS field_name,
            [day] AS field_value
        FROM ChoirOrder
    UNION ALL
        SELECT
            [name],
            'cat_' & LCase([cat]) & '_h_start' AS field_name,
            [h_start] AS field_value
        FROM ChoirOrder
    UNION ALL
        SELECT
            [name],
            'cat_' & LCase([cat]) & '_h_end' AS field_name,
            [h_end] AS field_value
        FROM ChoirOrder
    UNION ALL
        SELECT
            [name],
            'cat_' & LCase([cat]) & '_order' AS field_name,
            [ord] AS field_value
        FROM ChoirOrder
    ) AS u
GROUP BY [name]
PIVOT field_name IN
    (
        '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  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    2014-03-04  09:00          10:00        1            2014-03-05  14:00          15:00        1                                                               
C2    2014-03-04  10:00          11:00        2            2014-03-05  15:00          16:00        2                                                               
C3    2014-03-04  11:00          12:00        3                                                                 2014-03-06  09:00          10:00        1          
C4    2014-03-04  12:00          13:00        4                                                                 2014-03-06  12:00          13:00        4          
C5                                                         2014-03-05  16:00          17:00        3            2014-03-06  10:00          11:00        2          
C6                                                                                                              2014-03-06  11:00          12:00        3          
person Gord Thompson    schedule 04.03.2014