显示数据库中任何其他表中不存在的记录 [英] To display records that is not present in any other table in the database

问题描述

我有三个表，即contact，schoolcontact和userdetails，其中所有三个表都通过ContactID列链接。现在我想找到仅存在于表Contact中的记录，而不是userdetails和schoolcontact。你能帮我查一下这个问题。我尝试了下面的查询

I have three tables namely contact, schoolcontact and userdetails where all the three tables are linked by the column ContactID. Now I want to find the record that are present only in the table Contact and not in userdetails and schoolcontact. Can you please help me to query this problem. I tried the follwing query

select * from [TflStars].[dbo].[Contact] 
where [ContactID] NOT IN ( select [ContactID] from [TflStars].[dbo].[UserDetail] AND select [ContactID] from [TflStars].[dbo].[SchoolContact]) 

它显示关键字'AND'附近的语法错误

It shows an syntax error near the keyword 'AND'

推荐答案

有几种方法可以做到这一点。你是在正确的轨道，但只需要打破WHERE子句中的SELECT语句。



例如：

There are several ways to do it. You are on the right track but just need to break up your SELECT statements in the WHERE clause.

For example:

SELECT *
FROM Contact
WHERE ContactID NOT IN (SELECT ContactID FROM UserDetail)
AND ContactID NOT IN (SELECT ContactID FROM SchoolContact)

或另一种方式：

Or another way:

SELECT *
FROM Contact c 
LEFT JOIN UserDetail d ON c.ConactID = d.ContactID  
LEFT JOIN SchoolContact s ON c.ContactID = s.ContactID
WHERE d.ContactID IS NULL AND s.ContactID IS NULL -- since they are left joined they will be null if no match

甚至：

Or even:

SELECT *
FROM Contact
WHERE ContactID NOT IN (
  SELECT ContactID FROM UserDetail
  UNION 
  SELECT ContactID FROM SchoolContact )

我会测试每个案例，以确保它们在数据库中都不会太慢。

I would test each case to make sure none of them are too slow in your database.

支架有一个小的修正。这将消除错误并显示正确的结果。

Hi,

There is a small correction of bracket. That will get rid of the error and show you the correct result.

select * from [TflStars].[dbo].[Contact]
where [ContactID] NOT IN ( select [ContactID] from [TflStars].[dbo].[UserDetail]) AND [ContactID] NOT IN (select [ContactID] from [TflStars].[dbo].[SchoolContact])

但内部查询可能会变慢下来执行查询。所以你有另外一种选择除和联合的组合。请尝试以下查询。

But inner query may slow down the execution of query. So you have another option of combination of "except" and "union". Please try below query.

select ContactID  from [TflStars]
 EXCEPT
(SELECT ContactID FROM UserDetail
  UNION
 SELECT ContactID FROM SchoolContact
)

如果您有任何疑虑或疑问，或者您仍然面临问题，请与我们联系。



谢谢

Advay Pandya

Please let me know if you have any concern or query or if you are still facing issue.

Thanks
Advay Pandya

这篇关于显示数据库中任何其他表中不存在的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！