mysql - 从 2 个不同的表收集数据，条件如下

Question

我有 2 个 MySQL 表。一种是 pastsergicalhistory_type，另一种是 pastsurgicalhistory

下面是pastsergicalhistory_type

CREATE TABLE `pastsergicalhistory_type` (
 `idPastSergicalHistory_Type` int(11) NOT NULL AUTO_INCREMENT,
 `idUser` int(11) DEFAULT NULL,
 `Name` varchar(45) NOT NULL,
 PRIMARY KEY (`idPastSergicalHistory_Type`),
 KEY `fk_PastSergicalHistory_Type_User1_idx` (`idUser`),
 CONSTRAINT `fk_PastSergicalHistory_Type_User1` FOREIGN KEY (`idUser`) REFERENCES `user` (`idUser`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

以下是过去的外科历史

CREATE TABLE `pastsurgicalhistory` (
 `idPastSurgicalHistory` int(11) NOT NULL AUTO_INCREMENT,
 `idPatient` int(11) NOT NULL,
 `idPastSergicalHistory_Type` int(11) NOT NULL,
 `Comment` varchar(45) DEFAULT NULL,
 `ActiveStatus` tinyint(1) NOT NULL,
 `LastUpdated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`idPastSurgicalHistory`),
 KEY `fk_PastSurgicalHistory_Patient1_idx` (`idPatient`),
 KEY `fk_PastSurgicalHistory_PastSergicalHistory_Type1_idx` (`idPastSergicalHistory_Type`),
 CONSTRAINT `fk_PastSurgicalHistory_PastSergicalHistory_Type1` FOREIGN KEY (`idPastSergicalHistory_Type`) REFERENCES `pastsergicalhistory_type` (`idPastSergicalHistory_Type`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_PastSurgicalHistory_Patient1` FOREIGN KEY (`idPatient`) REFERENCES `patient` (`idPatient`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

现在我的要求是这样的，我会以点的形式解释一下。

1. 从 pastsergicalhistory_type 获取所有数据，其中 idUser 为 NULL 或 idUser 为 1。
2. 从 pastsurgicalhistory 获取所有数据，其中 idPatient 为 2。

如您所见，pastsurgicalhistory 的外键是 pastsergicalhistory_type 的主键。

我尝试了以下查询，但它给出了错误的结果。它仅显示 pastsurgicalhistory 中可用的内容。不显示 pastsurgicalhistory_type(遵循第 1 点中的条件)中不在 pastsurgicalhistory 中的数据。

 SELECT pastsergicalhistory_type.*,
    pastsurgicalhistory.*
    FROM pastsergicalhistory_type
    LEFT JOIN pastsurgicalhistory ON pastsurgicalhistory.`idPastSergicalHistory_Type` = pastsergicalhistory_type.`idPastSergicalHistory_Type`
    WHERE pastsergicalhistory_type.idUser = NULL OR pastsergicalhistory_type.idUser=1 AND pastsurgicalhistory.idPatient=2

那么，我该如何解决这个问题呢？

编辑

如果我在 where 子句中使用 AND Pastsurgicalhistory.idPatient=2，它实际上会过滤“整个”结果集。这将为我提供 idPatient 与 2 相关的结果。但正如我提到的，我也需要 pastsurgicalhistory 表中不可用的数据。

Answer 1

尝试

  SELECT pastsergicalhistory_type.*,
    pastsurgicalhistory.*
    FROM pastsergicalhistory_type
    LEFT JOIN pastsurgicalhistory ON  
   (pastsurgicalhistory.`idPastSergicalHistory_Type` = 
      pastsergicalhistory_type.`idPastSergicalHistory_Type` and 
     pastsurgicalhistory.idPatient=2)
   WHERE (pastsergicalhistory_type.idUser = NULL OR
   pastsergicalhistory_type.idUser=1)  ;