none
How to display Columns based on other table values

    Question

  • i have below tables
    1) student (sno,name,subject,cost,city),
    2) studentPT (sno,Ptname,EnrollDate)
    3) StudentDrill (Sno, DrillName,EnrollDate)

    now the requirement is want to display sno,name,cost,PTCol (if record exist(based on sno) in studentPt table then 'Yes' + EnrollDate otherwise 'Not Enrolled'), DrillCol (if record exist(based on sno) in studentDrill table then 'Yes' + EnrollDate otherwise 'Not Enrolled')

    how can i display those last two columsn based on rules

    please kindly help me

    Thank you Very Much in Advance
    Asittii

    Wednesday, February 27, 2013 7:27 PM

Answers

  • SELECT s.sno,s.name,s.cost, 
    CASE WHEN sp.sno is not null Then 'Yes ' + Convert(varchar(10),sp.EnrollDate,100) Else 'Not Enrolled' END as PTCol, sd.DrillCol
    FROM student s 
    LEFT JOIN studentPT sp On s.sno=sp.sno
    LEFT JOIN StudentDrill sd On s.sno=sd.sno
    

    • Marked as answer by asitti7 Wednesday, February 27, 2013 9:47 PM
    Wednesday, February 27, 2013 7:46 PM
    Moderator

All replies

  • i am tryiong this way,

    am i doing correct

    select
    sno,
    name,
    enroll,
    (Case when noMail.enroldate IS NULL then 'N' else 'Y' + CAST( noMail.enrollDate as varchar(20) ) end) as PTCol,
    (Case when Mail.enroldate IS NULL then 'N' else 'Y' + CAST( Mail.enrollDate as varchar(20) ) end) as DrillCol,

    from dbo.student t left join dbo.studentPT noMail on t.sno = noMail.sno
    left join dbo.studentDrill Mail on t.sno = Mail.sno

    do i need toc hange anything for correct results such as (case when ??)

    please help me

    thanks

    asitti

    Wednesday, February 27, 2013 7:41 PM
  • SELECT s.sno,s.name,s.cost, 
    CASE WHEN sp.sno is not null Then 'Yes ' + Convert(varchar(10),sp.EnrollDate,100) Else 'Not Enrolled' END as PTCol, sd.DrillCol
    FROM student s 
    LEFT JOIN studentPT sp On s.sno=sp.sno
    LEFT JOIN StudentDrill sd On s.sno=sd.sno
    

    • Marked as answer by asitti7 Wednesday, February 27, 2013 9:47 PM
    Wednesday, February 27, 2013 7:46 PM
    Moderator
  • thank you jingyang

    i greatful to you

    Best Regards

    asitti

    Wednesday, February 27, 2013 9:47 PM