不会返回预期返回的sql请求 [英] sql request that doesn't return which it's expected to return

问题描述

我对当前请求有问题，但没有返回我希望的结果

 选择 *  FROM 培训其中 _ID 不  IN (>选择 Training_id培训来自培训)和 _ID  IN (选择 _ID 从 TrainingManager 其中 idManager = 3)

该请求旨在检索由经理创建的所有id = 3(此条件由"where"之后的第二个条件解释)且未分配给其他已取消培训的培训(由表中包含where的第一个条件解释)如果未取消培训，则"Training_idTraining"列为null，如果将来的培训被取消，则将来的培训将替换为当前培训，如果培训未取消，则该ID将被填充.
当我删除第二个条件时，我得到了经理创建的所有培训，但是当我添加第一个条件以获取未分配给任何先前取消的培训的所有条件时，我没有任何记录. h2_lin>解决方案

您的要求不是很明确.但这就是我从您的问题中读到的内容:

  SELECT 
    *
 FROM 
    培训 AS  T  INNER   JOIN  TrainingManager  AS  TM 打开
        T._ID = TM._ID
         AND  TM.idManager =  3 
位置
    T.Training_idTraining<>  NULL  - 如果取消培训，则为NULL  

尝试一下:

  SELECT  T. *，TM.*
 FROM 培训 AS  T  LEFT  加入 TrainingManager  AS  TM  ON  T._ID = TM. _ID
位置 不 T.Training_id培训 IS   NULL   AND  TM.idManager =  3  

这是解决我的请求的代码sql:

<pre lang="SQL">SELECT * FROM Training where _ID NOT IN( select Training_idTraining from Training where Training_idTraining is not null) and _ID IN(select _ID from TrainingManager where idManager=3) and Training_idTraining is null

i have a problem with the current request that doesn''t return what i hope

SELECT * FROM Training where _ID NOT IN( select Training_idTraining from Training) and _ID IN(select _ID from TrainingManager where idManager=3)

the request aims to retrieve all the training created by manager with id = 3(this condition explained by second condition after the "where") and that are not assigned to other canceledtraining (explained by the first condition "after the where the table training contains the column "Training_idTraining" which is null if the training is not canceled and filled with the id the future training which will replace the current training if the later was canceled.
when i erase the second condition i get all trainings created by the manager but when i add the first condition to get all the one that aren''t assigned to any previous canceled training i don''t any record.

解决方案

Your requirements are not very clear. But this should be it if it is what I read from your question:

SELECT
    *
FROM
    Training AS T INNER JOIN TrainingManager AS TM ON
        T._ID = TM._ID
        AND TM.idManager = 3
WHERE
    T.Training_idTraining <> NULL -- this is NULL if the training is cancelled

Try this:

SELECT T.*, TM.*
FROM  Training AS T LEFT JOIN TrainingManager AS TM ON T._ID = TM._ID
WHERE NOT T.Training_idTraining IS NULL AND TM.idManager = 3

this is the code sql that solve my request:

<pre lang="SQL">SELECT * FROM Training where _ID NOT IN( select Training_idTraining from Training where Training_idTraining is not null) and _ID IN(select _ID from TrainingManager where idManager=3) and Training_idTraining is null

这篇关于不会返回预期返回的sql请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！