EXISTS clause help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

EXISTS clause help

Author

Topic

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-11-16 : 14:33:25

I am using following sub-quary in my Case statement. The sub quary does not get any results, but in case statement it still assigns the value as if 'EXISTS' is true.

select d.studentno from
dbo.tblS S
inner join tblD D
on S.StudentNo = D.studentno
where (D.exitdate is null or (D.entrydate <= '20120820' and D.exitdate >= '20120820'))
and S.StudentNo = '402886';

Using this in following select statment -
SELECT ID, FN, LN
(case when schno = '100' then '210'
else
when EXISTS (select d.studentno from
dbo.tblS S
inner join tblD D
on S.StudentNo = D.studentno
where (D.exitdate is null or (D.entrydate <= '20120820' and D.exitdate >= '20120820'))) then '250'
end) D_TYPE
FROM dbo.tblS WHERE StudentNo = '402886';

I get the result for D_TYPE = 250, while I expect nothing there for this student which does not exist in tblD.
Please help!

Niki

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-16 : 14:43:21

Your "sub-query" is not corrilated. You will notice that your sub-query does not referece that table or tables that you are actually selecting from (the rest of the query that you didn't post). So, the sub-query is showing that a values exists for that time-frame:

select d.studentno 
from dbo.tblS S
inner join tblD D 
on S.StudentNo = D.studentno 
where (D.exitdate is null or (D.entrydate <= '20120820' and D.exitdate >= '20120820'))
AND S.StudentNo = ??

You need to corrilate the StudentNo to the other table/tables

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-11-16 : 15:39:11

The main quary is for all students, and in above subquary I am just checking one student that I know should not have value 250. The main quary is selecting data elements (ID,FN,LN etc & many more ) from tblS & various other tables. It is huge quary so I did not put everything in there.

Niki

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-11-16 : 16:22:43

I'm not sure what you are saying. Do you understand what the issue is? You've provided a partial query that has syntax and logic issues. I *think* I understand what you are trying, but you have provided no DDL, DML or expected output. Here are some links that can help you understand how to ask a database question better so we canhelp you more easily:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Here is some sample code, similar to what you are doing, that shows the difference having the EXISTS clause correlated to the dominate table:

DECLARE @Student TABLE
(
    StudentNo INT NOT NULL,
    ExitDate DATETIME
)

DECLARE @OtherTable TABLE
(
    StudentNo INT,
    FN CHAR(1),
    LN CHAR(1),
    schno CHAR(3)
)

INSERT @Student VALUES
(1, '2011-01-01'),
(2, '2012-01-01'),
(3, '2013-01-01'),
(4, '2014-01-01'),
(5, '2015-01-01')

INSERT @OtherTable VALUES
(1, 'A', 'B', '100'),
(2, 'A', 'B', '200'),
(3, 'A', 'B', '200'),
(4, 'A', 'B', '200'),
(5, 'A', 'B', '100')


-- Show that we get two rows
SELECT *
FROM @Student
WHERE ExitDate BETWEEN '2012-01-01' AND '2013-01-01' 
    
-- UN-Correlated 
SELECT 
    O.*,
    CASE 
        WHEN O.schno = '100' THEN '210'
        WHEN EXISTS 
        (
            SELECT *
            FROM @Student
            WHERE ExitDate BETWEEN '2012-01-01' AND '2013-01-01' 
        )
        THEN '300'
    END AS D_Type
FROM 
    @OtherTable AS O

-- Correlated 
SELECT 
    O.*,
    CASE 
        WHEN O.schno = '100' THEN '210'
        WHEN EXISTS 
        (
            SELECT *
            FROM @Student AS S
            WHERE ExitDate BETWEEN '2012-01-01' AND '2013-01-01' 
            AND S.StudentNo = O.StudentNo
        )
        THEN '300'
    END AS D_Type
FROM 
    @OtherTable AS O

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-11-16 : 17:37:37

Thank you Lamprey, it makes sense. Sorry I yet have to learn how to ask my questions. Here is what I am doing, I beleive mine is correlated.

DECLARE @Student TABLE
(
StudentNo INT NOT NULL,
FN CHAR(10),
FN CHAR(10),
schno CHAR(3)
)

DECLARE @OtherTable TABLE
(
StudentNo INT,
ExitDate DATETIME
)

INSERT @Student VALUES
(1, 'John', 'Smith', '100'),
(2, 'Chuck', 'Doe', '200'),

INSERT @OtherTable VALUES
(3, '2013-01-01'),
(4, '2014-01-01'),
(5, '2015-01-01')

SELECT
s.StudentNo,FN,LN
CASE
WHEN s.schno = '100' THEN '210'
WHEN EXISTS
(
SELECT o.StudentNo
FROM @OtherTable AS o
WHERE ExitDate BETWEEN '2012-01-01' AND '2013-01-01'
AND s.StudentNo = o.StudentNo
)
THEN '300'
END AS D_Type
FROM
@Student AS s

Niki

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-16 : 22:23:03

the latest posted code looks fine, are you still having issues in getting correct values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-11-19 : 11:01:35

Yes, Visakh. Another DBA is looking at it; will let you know what we find out.

Niki

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-20 : 01:45:39

Cool...let us know if you face any more issues

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources