从子查询获取多个值的解决方法

Question

这是我的sql查询：

SELECT c.*, CAST ( 0 as int ) Score 
FROM Caregiver c JOIN Elderly e ON EXISTS 
( 
 SELECT x.LanguageID FROM 
 ( 
  SELECT 1 AS LanguageID WHERE e.Chinese = 1 UNION ALL 
  SELECT 2 AS LanguageID WHERE e.Malay = 1 UNION ALL 
  SELECT 3 AS LanguageID WHERE e.Tamil = 1 UNION ALL 
  SELECT 4 AS LanguageID WHERE e.English = 1 UNION ALL 
  SELECT 5 AS LanguageID WHERE e.Others = 1
 ) 
 x INTERSECT SELECT y.LanguageID FROM 
 ( 
  SELECT 1 AS LanguageID WHERE c.Chinese = 1 UNION ALL 
  SELECT 2 AS LanguageID WHERE c.Malay = 1 UNION ALL 
  SELECT 3 AS LanguageID WHERE c.Tamil = 1 UNION ALL 
  SELECT 4 AS LanguageID WHERE c.English = 1 UNION ALL 
  SELECT 5 AS LanguageID WHERE c.Others = 1
 ) 
 y 
) 
WHERE e.NRIC=@nric2 
AND c.CaregiverID != (SELECT CaregiverID FROM RequestPairing WHERE ReqID=@reqid2)

这不起作用，因为子查询( SELECT CaregiverID FROM RequestPairing WHERE ReqID=@reqid2)返回多个值。

我的目的是利用子查询来排除主查询返回的某些行。

那么有什么解决方法吗？

Answer 1

您可以将

此条件更改为NOT EXISTS：

WHERE e.NRIC=@nric2 
AND NOT EXISTS (SELECT CaregiverID FROM RequestPairing 
WHERE ReqID=@reqid2 AND CaregiverID = c.CaregiverID)

Answer 2

我想

你想要not in：

WHERE e.NRIC=@nric2 and
      c.CaregiverID not in (SELECT CaregiverID FROM RequestPairing WHERE ReqID=@reqid2)

Answer 3

是的。代替 != ，您可以在选择查询两边使用带有括号的NOT IN。