March 4, 2013 at 7:23 pm
Hello Please help me here.,
i have below tables called
student (sno, sname, address)
StudentDrillEnrolled (sno, DrillClass)
StudentDrillNotEnrolled (Sno, Reason, Viever)
how to write a query, so that i will have sno,sname,address, Enrolled (Y/N/NA)
enrolled will be calculated
if sno exist in StudentDrillEnrolled table then Y
if sno exist in StudentDrillNotEnrolled table then it is N
if not exist in both tables enrolled & not enrolled then N/A
please help me here
Thanks in advance
asitti
March 5, 2013 at 2:01 am
Sounds like you need a couple of left joins and a case - this should do...
SELECT
s.sno
,CASEWHEN e.sno IS NOT NULL THEN
'Y'
WHEN n.sno IS NOT NULL THEN
'N'
WHEN e.sno IS NULL AND n.sno IS NULL THEN
'N/A'
END
FROM student s
LEFT JOIN StudentDrillEnrolled e
ON s.sno=e.sno
LEFT JOIN StudentDrillNotEnrolled n
ON s.sno=n.sno;
March 5, 2013 at 4:09 pm
Or you can use EXISTS:
SELECT
sno, sname, address,
CASE
WHEN EXISTS(SELECT 1 FROM dbo.StudentDrillEnrolled sde WHERE sde.sno = s.sno) THEN 'Y'
WHEN EXISTS(SELECT 1 FROM dbo.StudentDrillNotEnrolled sdne WHERE sdne.sno = s.sno) THEN 'N'
ELSE 'N/A'
END AS Enrolled
FROM dbo.student s
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy