MS Sql EXISTS条件 [英] MS Sql EXISTS condition

问题描述

我有两个桌子
1)公司

Hi,

I have two table
1) Company

Company_id Int
Name       Varchar
Address    Varchar

2)会议

2) Meeting

Meeting_Id Int
Company_id Int
Start_date datetime
End_date   datetime
Country_id int
state_id   int

我的sql就像下面的

my sql is like below

SELECT *
FROM   Company C
WHERE  c.name LIKE 'a%'
    AND EXISTS
        (
            SELECT 1
            FROM   Meeting M
            WHERE  c.Company_id = M.Company_id
                AND M.Country_id = 2
                AND M.State_id = 10
        )
    AND EXISTS
        (
            SELECT 1
            FROM   Meeting M
            WHERE  C.Company_id = M.Company_id
                AND M.start_date>= '2011-01-01'
                AND m.end_date<= '2011-12-31'
        )

问题是，当我运行此查询结果时，该查询结果将与以下查询的结果数据不匹配.

Issue is when i run this query result will not match with below query''s result data.

SELECT *
FROM   Company C
WHERE  c.name LIKE 'a%'
    AND EXISTS
        (
            SELECT 1
            FROM   Meeting M
            WHERE  c.Company_id = M.Company_id
                AND M.Country_id = 2
                AND M.State_id = 10
                AND M.start_date>= '2011-01-01'
                AND m.end_date<= '2011-12-31'
        )

我只想知道我在此查询中遗漏了什么?

I just want to know that i miss something in this query?

推荐答案

如果您参加两家公司的以下会议:

If you take the following meetings for two companies:

MeetingID CompanyID Startdate  EndDate    CountryID StateID

M1        C1        2010-02-01 2010-02-01 2         10
M2        C1        2011-08-07 2011-08-07 20        5
M3        C2        2011-10-09 2011-10-09 2         10

对于第一个选择语句，会议M1和M3符合第一个EXISTS语句的标准，而会议M2和M3符合第二个EXISTS语句的标准.因此，公司C1和C2都满足EXISTS语句，并显示在结果中.

现在，在第二条选择声明中，会议M1和M2不满足单个EXISTS声明的条件，并且未显示公司C1.因为满足M3的条件确实符合条件，所以结果中显示了公司C2.

For the first select statement the meetings M1 and M3 meet the criteria of the first EXISTS statement and meetings M2 and M3 meet the criteria of the second EXISTS statement. So companies C1 and C2 meet both EXISTS statements and are shown in the result.

Now in the second select statement meetings M1 and M2 do not meet the criteria of the single EXISTS statement and company C1 is not shown. Because meeting M3 does meet the criteria company C2 is shown in the result.

这篇关于MS Sql EXISTS条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！