SQL Query Prob

• tadhg88

  Ten Centuries

  Points: 1031

  More actions

  January 10, 2008 at 4:19 am

  #91486

  Hi i am, just wondering if anyone could help me with an sql query, there is one main table MedicalReferral and it joins 3 others that may have matching rows or not, i need to display View is there are matching rows or else No Report i have written this sql but it doesnt work any help would be appreciated

  Thanks in advance

  Tim

  Select

  case

  when rr.ReferralID is null then 'No Report1'

  when rrp.ReferralID is null then 'No Report2'

  when fluid.ReferralID is null then 'No Report3'

  else 'View'

  end as 'Report',

  rr.ReferralID,

  rrp.ReferralID,

  fluid.ReferralID

  From MedicalReferral mr

  Left Join ReferralReport rr on rr.ReferralID = mr.ID

  Left Join ReferralReportPathlab rrp on rrp.ReferralID = mr.ID

  Left Join ReferralReportJointFluidMicroscopy fluid on fluid.ReferralID = mr.ID

• John Mitchell-245523

  SSC Guru

  Points: 148786

  More actions

  January 10, 2008 at 4:31 am

  #766559

  What do you mean when you say it doesn't work? Does it give an error message, or does it just not return the results you expect? Please provide table structure for each table in the form of CREATE TABLE statements, sample data in the form of INSERT statements, and the results you expect to see given that sample data.

  Thanks

  John

• tadhg88

  Ten Centuries

  Points: 1031

  More actions

  January 10, 2008 at 5:26 am

  #766578

  thanks for ur reply but its actually ok now i managed to figure it out i just changed the case statement to be

  case

  when rr.ReferralID is not null then 'View'

  when rrp.ReferralID is not null then 'View'

  when fluid.ReferralID is not null then 'View'

  else 'No Report'

  Thanks again

  Tim