php - 如果不在另一个查询中，请选择 ROW

Question

我有这个问题:

query 1 = 
SELECT emp.employeId,emp.nom,emp.prenom,msg.note,msg.quelleDate, emp.remarques, emp.autonome, embauche.actif as embaucheActif, embauche.embId, 'M' as source 
FROM employes emp 
LEFT JOIN messages msg ON msg.employeId = emp.employeId 
LEFT JOIN embauche ON embauche.employeId = emp.employeId 
WHERE emp.actif = 1 
AND msg.quelleDate BETWEEN '2013-01-01' AND '2013-01-31' 

UNION 

SELECT emp.employeId,emp.nom,emp.prenom,msg.note,msg.quelleDate, emp.remarques, emp.autonome, embauche.actif as embaucheActif, embauche.embId, 'A' as source 
FROM employes emp 
LEFT JOIN messagesautre msg ON msg.employeId = emp.employeId 
LEFT JOIN embauche ON embauche.employeId = emp.employeId 
WHERE emp.actif = 1 
AND msg.quelleDate BETWEEN '2013-01-01' AND '2013-01-31' 

ORDER BY nom,prenom,quelleDate

还有这个查询

query2 = select nom,prenom from employes where actif = 1;

我正在尝试从 query2 中获取所有未出现在 query1 中的 employeeId

我试过

select employeId,nom,prenom from employes where actif = 1 AND employeId in ( query1 );

但这当然行不通，因为 query1 返回多于 1 列

是否可以使用 MySQL 查询来做到这一点？还是我必须编写 PHP 算法来执行此操作？？

Answer 1

这样做是否简单:

select nom,prenom from employes AS emp 
LEFT JOIN embauche AS emb  
ON emp.employeID = emb.employeID
LEFT JOIN messages AS msg
ON emp.employeID = msg.employeID
where actif <> 1
AND msg.quelleDate NOT BETWEEN '2013-01-01' AND '2013-01-31';

示例数据:

CREATE TABLE employes (
  employeId int auto_increment primary key,
  nom varchar(30),
  prenom varchar(30),
  remarques longtext,
  autonome longtext
  );

CREATE TABLE messages (
  messagesID int auto_increment primary key,
  employeId int,
  quelleDate DATE,
  note longtext

  );

CREATE TABLE embauche(
  embId int auto_increment primary key,
  employeID int,
  actif int
  );

INSERT INTO employes (nom,prenom,remarques,autonome)
VALUES ("hollande", "francois", "24th President of France", "?"),
("Sarkozy", "Nicolas", "23th President of France", "?");


INSERT INTO messages (employeId,quelleDate,note)
VALUES (1, now(),"jlkjlkahkjhkj"),
(2,NOW() - INTERVAL 365 DAY,"jlkjlkahkjkjjhajhjheuioueoijhkj");


INSERT INTO embauche (employeId,actif)
VALUES (2, 2),
(1,1);

Demo sql fiddle