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