mysql - SQL RIGHT JOIN 误解

Question

我正在开发其 SQL 后端 (MySQL 5.6) 有 4 个表的 ASP.NET 应用程序:第一个表是这样定义的:

CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `descr` varchar(45) NOT NULL,
  `modus` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
);

这些是在应用程序中管理的项目。

第二张表:

CREATE TABLE `files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_path` varchar(255) NOT NULL,
  `id_item` int(11) NOT NULL,
  `id_type` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
);

这些是项目管理所需的文件。每个 'item' 可以有 0 个或多个文件('id_item' 字段填充 'items' 表的有效 'id')。

第三张表:

CREATE TABLE `file_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_type` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
);

此表描述了文件的类型。

第四张表:

CREATE TABLE `checklist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_type` int(11)  NOT NULL,
  `modus` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
);

这个表，顾名思义，是一个 list 。它描述了需要为特定的“modus”收集哪些类型的文件，“modus”字段与“items”表中的“modus”具有相同的值，“id_type”包含来自“file_types”表的有效“id”值.

假设第一个表包含这些项目:

id  descr      modus
--------------------
1   First      M
2   Second     P
3   Third      M
4   Fourth     M
--------------------

第二个:

id  file_path       id_item    id_type
--------------------------------------
1   file1.jpg       1          1
2   file2.jpg       1          2
3   file3.jpg       2          1
4   file4.jpg       1          4
5   file5.jpg       1          1
--------------------------------------

第三个:

id  file_type
--------------
1   red
2   blue
3   green
4   default
--------------

第四张表:

id  id_type    modus
--------------------
1   1          M
2   2          M
3   3          M
4   4          M
5   1          P
6   4          P
--------------------

我需要得到的是一个包含这些项目的表(指id_item = 1):

id_item  file_path       id_type   file_type
--------------------------------------------
1        file1.jpg       1         red
1        file5.jpg       1         red
1        file2.jpg       2         blue
1        file4.jpg       4         default
<null>   <null>          3         green
--------------------------------------------

虽然 id_item = 2 的结果表应该如下所示:

id_item  file_path       id_type   file_type
--------------------------------------------
2        file3.jpg       1         red
<null>   <null>          4         default
--------------------------------------------

当然，“id_item”是“items”表的“id”，“id_type”是“types”表的“id”等。

简而言之，我需要一个表格来描述特定“项目”id 的 list 状态，即哪些文件已被收集，哪些文件丢失。

我尝试使用 RIGHT JOIN 子句但没有成功:

SELECT 
    items.id AS id_item,
    files.file_path AS file_path,
    file_types.id AS id_type,
    file_types.file_type AS file_type
FROM
    files
        RIGHT JOIN
    checklist ON (files.id_type = checklist.id_type )
        INNER JOIN
    items ON (files.id_item = items.id)
        AND (items.modus = checklist.modus)
        INNER JOIN
    file_types ON (checklist.id_type = file_types.id)
WHERE (items.id = 1);

这个查询的结果是:

 id_item   file_path    id_type    file_type
 ------------------------------------------
 1         file1.jpg    1          red
 1         file5.jpg    1          red
 1         file2.jpg    2          blue
 1         file4.jpg    4          default

它缺少最后一行( list 中缺少的文件)。

Answer 1

以下查询为您提供了以下每个项目的状态( list 的种类)。我不得不更改一些列名，这些列名在我的测试环境中是保留字。

select item_id,
       fp filepath,
       m_type,
       item_desc,
       modee,
       (select t.type from typess t where t.id = m_type)
  from (select null      item_id,
               i.descr   item_desc,
               c.modee   modee,
               c.id_type m_type,
               null      fp
          from items i, checklist c
         where c.modee = i.modee
           and i.id = 0
           and c.id_type not in
               (select f.id_type from files f where f.id_item = i.id)
        union all
        select i.id        item_id,
               i.descr     item_desc,
               c.modee     modee,
               c.id_type   m_type,
               f.file_path fp
          from items i, checklist c, files f
         where c.modee = i.modee
           and i.id = 0
           and f.id_item = i.id
           and f.id_type = c.id_type)
 order by item_id asc, m_type asc