Post reply

How to display Columns based on other table values

  • February 27, 2013 at 12:28 pm

    #274682

    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

  • February 27, 2013 at 12:37 pm

    #1591685

    Look at using LEFT OUTER JOINS and either ISNULL (SQL Server specific) or COALESCE (ANSI Standard).

  • February 27, 2013 at 12:54 pm

    #1591704

    hi lynn,

    i tried this way, am i doing correct , please correct me, is the case statement logic is good? or do i need to check some otherway

    please help me, it has to be run in optimized way,, so please suggest me i fi am doing anything wrong

    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

  • February 27, 2013 at 9:02 pm

    #1591839

    Cool, that's correct. Also, make it a practice to check out execution plans of all queries that you write, if you don't do that already (Ctrl + M).

    https://sqlroadie.com/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply