SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 EXISTS clause help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Niki
Yak Posting Veteran

51 Posts

Posted - 11/16/2012 :  14:33:25  Show Profile  Reply with Quote
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

Edited by - Niki on 11/16/2012 15:46:14

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 11/16/2012 :  14:43:21  Show Profile  Reply with Quote
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

Edited by - Lamprey on 11/16/2012 14:45:36
Go to Top of Page

Niki
Yak Posting Veteran

51 Posts

Posted - 11/16/2012 :  15:39:11  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 11/16/2012 :  16:22:43  Show Profile  Reply with Quote
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
Go to Top of Page

Niki
Yak Posting Veteran

51 Posts

Posted - 11/16/2012 :  17:37:37  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/16/2012 :  22:23:03  Show Profile  Reply with Quote
the latest posted code looks fine, are you still having issues in getting correct values?

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

Go to Top of Page

Niki
Yak Posting Veteran

51 Posts

Posted - 11/19/2012 :  11:01:35  Show Profile  Reply with Quote
Yes, Visakh. Another DBA is looking at it; will let you know what we find out.

Niki
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/20/2012 :  01:45:39  Show Profile  Reply with Quote
Cool...let us know if you face any more issues

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000