Write SQL Select statement with IF EXISTS sub query

Last Reply on Apr 12, 2014 03:39 AM By Mudassar

Posted on Apr 12, 2014 12:53 AM

studentdetail table asfollows

studid  mobile         course  description

1000   9874511212  MFA     master

 

smsdetail table asfollows

Keyword studid   mobile         replymsg

MFA      1000     9874511212 delivered

i want to getthe replymsg from smsdetail table. inthe above two table course and keyword matches i want to add newcolumn called status inthat status booked has to be inserted

query asfollows

select c.replymsg,s.studid,c.mobile from studentdetail s, smsdetail c where s.studid=c.studid and s.mobile=c.mobile

Output asfollows

replymsg studid mobile

delivered 1000  9874511212

inthe above two table MFA isthere.ifit matches i want to add newcolumn called status booked.

but i want output asfollows

replymsg studid mobile         Status

delivered 1000  9874511212  booked

forthat how can doinsql server.

Regards,

Narasiman P.

Posted on Apr 12, 2014 03:39 AM

This way

SELECT d.replymsg, s.studid, s.mobile
,(CASE WHEN ((SELECT CHARINDEX(s.coursecode, keyword) FROM SMSDetails WHERE studid= s.studid) > 0) THEN 'Booked' ELSE 'Not Booked' END) [Status]
FROM StudentDetails s INNER JOIN SMSDetails d ON s.studid = d.studid

Image: