Объединение таблиц, когда данные могут или не могут существовать

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

Я пытаюсь восстановить неудачи для набора велосипедов, где наиболее важным определяющим фактором является то, имеет ли какая-либо из частей велосипеда определенный атрибут. Атрибут устанавливается в таблице деталей. Часть является частью сборки, которая имеет ссылку на большую сборку. Узел может иметь определенный велосипедный тип, назначенный ему; если это не так, мы предполагаем, что ВСЕ типы велосипедов назначаются сборке. Части могут также иметь определенные велосипеды, назначенные им, обозначенные серийным номером.

Итак, мы можем предположить:

Запись в таблице сбоев всегда будет включать серийный номер, более высокую сборку и тип велосипеда.
У сборки деталей всегда будет ссылка на более высокую сборку
Комбинация деталей может иметь или не иметь ссылок на типы велосипедов.
Часть может иметь или не иметь ссылок на определенные серийные номера

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

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

Ниже приведен запрос.

SELECT f_bicycle_type, f_serial_number, f_big_assembly
FROM ( 
    SELECT DISTINCT f.f_bicycle_type, f.f_serial_number, f.f_big_assembly, p_important_attr 
    from failures f 
    left outer join (    
        select distinct bt.bt_bicycle_type, b_serial_number, a_big_assembly, p_important_attr  
        from (          
            select distinct b.b_serial_number, a.a_big_assembly, p.p_assembly_id, p.p_important_attr
            from parts p
            join assemblies a on p.p_assembly_id = a.a_assembly_id
            left outer join parts_bicycles b on b.b_part_id = p.p_id  
            where p.p_important_attr = 'awesome'
        ) p_join_a_and_b 
        left outer join assembly_bicycle_types bt on bt.bt_assembly_id = p_join_a_and_b.p_assembly_id 
    ) p_join_a_and_b_join_bt 
    on f.f_big_assembly = p_join_a_and_b_join_bt.a_big_assembly 
    -- problem join clause - if an explicit type has not been assigned to the assembly, we want to include ALL types
    and f_bicycle_type = p_join_a_and_b_join_bt.bt_bicycle_type
    -- problem join clause - there may not be explicit serial numbers assigned to a given part
    and f_serial_number = b_serial_number
) z
WHERE p_important_attr = 'awesome';

Тестовый код sql (для Oracle):

CREATE TABLE failures (
f_bicycle_type VARCHAR(20),
f_serial_number NUMBER(20),
f_big_assembly VARCHAR(5)); 

CREATE TABLE parts (
p_id NUMBER(20),
p_assembly_id NUMBER(20),
p_important_attr VARCHAR(20));

CREATE TABLE assemblies (
a_assembly_id NUMBER(20),
a_big_assembly VARCHAR(5)); 

CREATE TABLE parts_bicycles (
b_part_id NUMBER(20),
b_serial_number NUMBER(20));    

CREATE TABLE assembly_bicycle_types (
bt_assembly_id NUMBER(20),
bt_bicycle_type VARCHAR(20));

INSERT ALL
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('tandem', 1000001, 'A1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('bmx', 1000002, 'A1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('tandem', 1000003, 'B1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('cruiser', 1000004, 'B1000')  
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('bmx', 1000005, 'C1000')  
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('motocross', 1000006, 'C1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('cruiser', 1000007, 'C1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('bmx', 1000008, 'D1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('bmx', 1000009, 'D1000')
INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('cruiser', 1000010, 'E1000')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (1, 1001, 'awesome')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (2, 1001, 'ordinary')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (3, 2001, 'awesome')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (4, 3001, 'awesome')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (5, 4001, 'awesome')
INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (6, 5001, 'ordinary')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (1001, 'A1000')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (2001, 'B1000')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (3001, 'C1000')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (4001, 'D1000')
INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (5001, 'E1000')
INTO parts_bicycles (b_part_id, b_serial_number)
VALUES (4, 1000005)
INTO parts_bicycles (b_part_id, b_serial_number)
VALUES (4, 1000006)
INTO parts_bicycles (b_part_id, b_serial_number)
VALUES (5, 1000008)
INTO assembly_bicycle_types (bt_assembly_id, bt_bicycle_type)
VALUES (02001, 'tandem')
INTO assembly_bicycle_types (bt_assembly_id, bt_bicycle_type)
VALUES (04001, 'bmx')
SELECT * FROM DUAL;

Для MySQL:

 CREATE TABLE failures (
f_bicycle_type VARCHAR(20),
f_serial_number INTEGER(20),
f_big_assembly VARCHAR(5));
CREATE TABLE parts(
p_id INTEGER( 20 ) ,
p_assembly_id INTEGER( 20 ) ,
p_important_attr VARCHAR( 20 )
);
CREATE TABLE assemblies(
a_assembly_id INTEGER( 20 ) ,
a_big_assembly VARCHAR( 5 )
);
CREATE TABLE parts_bicycles(
b_part_id INTEGER( 20 ) ,
b_serial_number INTEGER( 20 )
);
CREATE TABLE assembly_bicycle_types(
bt_assembly_id INTEGER( 20 ) ,
bt_bicycle_type VARCHAR( 20 )
);

INSERT INTO failures (f_bicycle_type, f_serial_number, f_big_assembly)
VALUES ('tandem', 1000001, 'A1000'),('bmx', 1000002, 'A1000'), ('tandem', 1000003, 'B1000'),    ('cruiser', 1000004, 'B1000') ,('bmx', 1000005, 'C1000'), ('motocross', 1000006, 'C1000')
,('cruiser', 1000007, 'C1000')
,('bmx', 1000008, 'D1000')
,('bmx', 1000009, 'D1000')
, ('cruiser', 1000010, 'E1000');
insert INTO parts (p_id, p_assembly_id, p_important_attr)
VALUES (1, 1001, 'awesome'), (2, 1001, 'ordinary'), (3, 2001, 'awesome'), (4, 3001, 'awesome'), (5, 4001, 'awesome'),(6, 5001, 'ordinary');
INSERT INTO assemblies (a_assembly_id, a_big_assembly)
VALUES (1001, 'A1000'), (2001, 'B1000'), (3001, 'C1000'), (4001, 'D1000'),(5001, 'E1000');
    INSERT INTO parts_bicycles (b_part_id, b_serial_number)
VALUES (4, 1000005),(4, 1000006),(5, 1000008)
INSERT INTO assembly_bicycle_types (bt_assembly_id, bt_bicycle_type)
VALUES (02001, 'tandem'), (04001, 'bmx');

Пример данных и желаемых результатов:

-- failures table
-- f_bicycle_type   || f_serial_number  || f_big_assembly
---------------------------------------------------------
  tandem               1000001             A1000
  bmx                  1000002             A1000
  tandem               1000003             B1000
  cruiser              1000004             B1000
  bmx                  1000005             C1000
  motocross            1000006             C1000
  cruiser              1000007             C1000
  bmx                  1000008             D1000
  bmx                  1000009             D1000
  cruiser              1000010             E1000

  -- parts table
  -- p_id   || p_assembly_id    || p_important_attr
  ------------------------------------------------
     1          1001                awesome
     2          1001                ordinary
     3          2001                awesome
     4          3001                awesome
     5          4001                awesome
     6          5001                ordinary

  -- assemblies table
  -- a_assembly_id  || a_big_assembly
  -----------------------------------
     1001              A1000
     2001              B1000
     3001              C1000
     4001              D1000
     5001              E1000

  -- parts_bicycles table
  -- b_part_id  || b_serial_number
  --------------------------------
     4              1000005
     4              1000006
     5              1000008

  -- assembly_bicycle_types table
  -- bt_assembly_id || bt_bicycle_type
  ------------------------------------
     02001             tandem
     04001             bmx

-- desired results from failures table
-- f_bicycle_type   || f_serial_number  || f_big_assembly
---------------------------------------------------------
  tandem               1000001             A1000
  bmx                  1000002             A1000
  tandem               1000003             B1000
  bmx                  1000005             C1000
      motocross            1000006             C1000
  bmx                  1000008             D1000

И фактические результаты, когда проблема встает на свои места:

-- actual results from failures table
-- f_bicycle_type   || f_serial_number  || f_big_assembly
---------------------------------------------------------
  bmx                  1000008             D1000

Ответы

Ответ 1

Там вы идете (вкус PostgreSQL):

WITH chosen_parts AS (
  SELECT * FROM parts LEFT JOIN parts_bicycles ON b_part_id = p_id 
    WHERE p_important_attr = 'awesome'
), chosen_assemblies AS (
  SELECT * FROM assemblies JOIN chosen_parts ON p_assembly_id = a_assembly_id 
    LEFT JOIN assembly_bicycle_types ON bt_assembly_id = a_assembly_id 
  WHERE b_serial_number IS NULL
)
SELECT failures.* FROM chosen_parts JOIN failures 
  ON f_serial_number = b_serial_number
UNION
SELECT failures.* FROM chosen_assemblies JOIN failures 
  ON f_big_assembly = a_big_assembly 
  WHERE bt_bicycle_type = f_bicycle_type
    OR bt_bicycle_type IS NULL;

Не стесняйтесь добавлять внешний SELECT DISTINCT * FROM, если проблемы повторяются.

Ответ 2

Этот запрос объединяет детали, части_обязания, сборки, assembly_bicycle_type. Сохраните его как представление:

create view j_parts as
select p_important_attr, b_serial_number, a_big_assembly, bt_bicycle_type
from
  parts left join parts_bicycles
    on parts.p_id = parts_bicycles.b_part_id
  left join assemblies
    on parts.p_assembly_id=assemblies.a_assembly_id
  left join assembly_bicycle_types
    on assemblies.a_assembly_id =assembly_bicycle_types.bt_assembly_id

и это (я думаю!) - это запрос, который дает вам ваш желаемый результат:

SELECT failures.*
FROM
  failures inner join j_parts
  on f_serial_number=b_serial_number
     and p_important_attr = 'awesome'
UNION
SELECT failures.*
FROM
  failures inner join j_parts
  on f_big_assembly=a_big_assembly
     and b_serial_number is null
     and j_parts.bt_bicycle_type=f_bicycle_type
     and p_important_attr = 'awesome'
UNION
SELECT failures.*
FROM
  failures inner join j_parts
  on f_big_assembly=a_big_assembly
     and b_serial_number is null
     and j_parts.bt_bicycle_type is null
     and p_important_attr = 'awesome'

РЕДАКТИРОВАТЬ: Я хотел написать так, потому что его легче читать и читать. Затем запрос можно оптимизировать. Здесь есть все условия только в одном выборе:

SELECT failures.*
FROM
  failures inner join
  (parts left join parts_bicycles
   on parts.p_id = parts_bicycles.b_part_id
   left join assemblies
   on parts.p_assembly_id=assemblies.a_assembly_id
   left join assembly_bicycle_types
   on assemblies.a_assembly_id =assembly_bicycle_types.bt_assembly_id)
  on f_serial_number=b_serial_number
     or (f_big_assembly=a_big_assembly
         and b_serial_number is null
         and (bt_bicycle_type=f_bicycle_type
              or bt_bicycle_type is null))
  and p_important_attr = 'awesome'

Ответ 3

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

SELECT DISTINCT f.f_bicycle_type, f.f_serial_number, f.f_big_assembly
FROM  parts p
      LEFT JOIN parts_bicycles pb
         ON p.p_id = pb.b_part_id
      LEFT JOIN assemblies a
         ON p.p_assembly_id = a.a_assembly_id
      LEFT JOIN assembly_bicycle_types abt
         ON a.a_assembly_id = abt.bt_assembly_id
      LEFT JOIN failures f
         ON -- First priority is parts that map directly
            pb.b_serial_number = f.f_serial_number 
            -- Second priority is assemblies that map to type
            OR (pb.b_serial_number IS NULL 
                AND abt.bt_bicycle_type = f.f_bicycle_type) 
            -- Third priority is assemblies that map directly
            OR (pb.b_serial_number IS NULL 
                AND abt.bt_bicycle_type IS NULL 
                AND a.a_big_assembly = f.f_big_assembly)
WHERE  p.p_important_attr = 'awesome'
ORDER BY f.f_serial_number

SQL Fiddle

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

Ответ 4

измененный запрос может быть

SELECT f_bicycle_type, f_serial_number, f_big_assembly
FROM ( 
SELECT DISTINCT f.f_bicycle_type, f.f_serial_number, f.f_big_assembly, p_important_attr 
from failures f 
left outer join (    
    select distinct bt.bt_bicycle_type, b_serial_number, a_big_assembly, p_important_attr  
    from (          
        select distinct b.b_serial_number, a.a_big_assembly, p.p_assembly_id, p.p_important_attr
        from parts p
        join assemblies a on p.p_assembly_id = a.a_assembly_id
        left outer join parts_bicycles b on b.b_part_id = p.p_id  
        where p.p_important_attr = 'awesome'
    ) p_join_a_and_b 
    left join assembly_bicycle_types bt on bt.bt_assembly_id = p_join_a_and_b.p_assembly_id 
) p_join_a_and_b_join_bt 
on f.f_big_assembly = p_join_a_and_b_join_bt.a_big_assembly 
-- problem join clause - if an explicit type has not been assigned to the assembly, we want to include ALL types
and (f_bicycle_type = p_join_a_and_b_join_bt.bt_bicycle_type or p_join_a_and_b_join_bt.bt_bicycle_type is null)
-- problem join clause - there may not be explicit serial numbers assigned to a given part
and (f_serial_number = b_serial_number or b_serial_number is null)
) z
WHERE p_important_attr = 'awesome';