how to write a column expression in a query based on other table value exist

• asita

  SSCarpal Tunnel

  Points: 4144

  More actions

  March 4, 2013 at 7:23 pm

  #274821

  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

• Hotblack

  SSC Veteran

  Points: 228

  More actions

  March 5, 2013 at 2:01 am

  #1593323

  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;

• ScottPletcher

  SSC Guru

  Points: 100948

  More actions

  March 5, 2013 at 4:09 pm

  #1593680

  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) A socialist is someone who will give you the shirt off *someone else's* back.