Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to write a column expression in a query based on other table value exist Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 7:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 12:47 PM
Points: 141, Visits: 282
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
Post #1426559
Posted Tuesday, March 5, 2013 2:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 2, 2014 8:18 AM
Points: 169, Visits: 106
Sounds like you need a couple of left joins and a case - this should do...

SELECT
s.sno
,CASE WHEN 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;



Post #1426647
Posted Tuesday, March 5, 2013 4:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 2,266, Visits: 3,419
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1427072
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse