基于連接過濾表但選擇所有連接而不考慮過濾器

我使用 Typeorm 查詢生成器生成了以下查詢

當前行為：現在發生的情況是，海軍陸戰隊已正確過濾，但選定的連接也按給定條件過濾。只回傳符合條件的碼頭，而不是所有屬于海軍陸戰隊的碼頭。

預期結果：我想獲得所有擁有至少一個名為“dock 1”的碼頭的海軍陸戰隊。我想包括屬于那個海軍陸戰隊的所有碼頭，而不考慮他們的名字。

我應該如何執行這個查詢？

任何幫助將非常感激。

mariadb 版本 15.1

[email protected]

SELECT `c`.`id`                AS `c_id`,
       `c`.`name`              AS `c_name`,
       `c`.`description`       AS `c_description`,
       `c`.`FkId`              AS `c_FkId`,
       `c`.`FkModel`           AS `c_FkModel`,
       `c`.`isActive`          AS `c_isActive`,
       `c`.`createdAt`         AS `c_createdAt`,
       `c`.`updatedAt`         AS `c_updatedAt`,
       `c`.`phones`            AS `c_phones`,
       `c`.`emails`            AS `c_emails`,
       `c`.`mainImage`         AS `c_mainImage`,
       `c`.`galleryImages`     AS `c_galleryImages`,
       `c`.`addressDetailsId`  AS `c_addressDetailsId`,
       `c_Docks`.`id`          AS `c_Docks_id`,
       `c_Docks`.`name`        AS `c_Docks_name`,
       `c_Docks`.`description` AS `c_Docks_description`,
       `c_Docks`.`FkId`        AS `c_Docks_FkId`,
       `c_Docks`.`FkModel`     AS `c_Docks_FkModel`,
       `c_Docks`.`isActive`    AS `c_Docks_isActive`,
       `c_Docks`.`createdAt`   AS `c_Docks_createdAt`,
       `c_Docks`.`updatedAt`   AS `c_Docks_updatedAt`,
       `c_Docks`.`MarineId`    AS `c_Docks_MarineId`
FROM `marine` `c`
         LEFT JOIN `dock` `c_Docks` ON `c_Docks`.`MarineId` = `c`.`id`
WHERE `c`.`isActive` = true
  AND (`c_Docks`.`name` = 'dock 1')
# ORDER BY c_id DESC

uj5u.com熱心網友回復：

步驟 1：從表中過濾掉所有相關的行：來自 Marine C where c。isActive= true
第 2 步：使之前的 SQL 成為行內視圖，并將其與表停靠欄左連接，并在停靠表上應用過濾器。因此，SQL 應如下所示：

SELECT          c.<column_names> ,
                c_docks.<column_names>
FROM            (
                       SELECT <column_names>
                       FROM   marine
                       WHERE  isActive = true) c
LEFT OUTER JOIN dock c_docks
ON              c.id = c_docks.marine_id
AND             c_docks.NAME = 'dock 1';

uj5u.com熱心網友回復：

您可以加入dock兩次。一次過濾碼頭名稱，另一次獲取所有碼頭。

SELECT `c`.`id`                AS `c_id`,
       `c`.`name`              AS `c_name`,
       `c`.`description`       AS `c_description`,
       `c`.`FkId`              AS `c_FkId`,
       `c`.`FkModel`           AS `c_FkModel`,
       `c`.`isActive`          AS `c_isActive`,
       `c`.`createdAt`         AS `c_createdAt`,
       `c`.`updatedAt`         AS `c_updatedAt`,
       `c`.`phones`            AS `c_phones`,
       `c`.`emails`            AS `c_emails`,
       `c`.`mainImage`         AS `c_mainImage`,
       `c`.`galleryImages`     AS `c_galleryImages`,
       `c`.`addressDetailsId`  AS `c_addressDetailsId`,
       `c_Docks`.`id`          AS `c_Docks_id`,
       `c_Docks`.`name`        AS `c_Docks_name`,
       `c_Docks`.`description` AS `c_Docks_description`,
       `c_Docks`.`FkId`        AS `c_Docks_FkId`,
       `c_Docks`.`FkModel`     AS `c_Docks_FkModel`,
       `c_Docks`.`isActive`    AS `c_Docks_isActive`,
       `c_Docks`.`createdAt`   AS `c_Docks_createdAt`,
       `c_Docks`.`updatedAt`   AS `c_Docks_updatedAt`,
       `c_Docks`.`MarineId`    AS `c_Docks_MarineId`
FROM `marine` `c`
JOIN `dock` `c_Docks` ON `c_Docks`.`MarineId` = `c`.`id`
JOIN `dock` `dock_1` ON `dock_1`.`MarineId` = `c`.`id`
WHERE `c`.`isActive` = true
  AND `dock_1`.`name` = 'dock 1'

沒有理由在LEFT JOIN這里使用，因為您不想包含沒有匹配dock.

標籤：mysql sql 玛丽亚数据库 打字机

上一篇：如何使用SQL子查詢來限制父查詢中回傳值的范圍？

下一篇：雪花中的值