Include blank ticket field values in Helpdesk report

[harringg]

When creating a Helpdesk Report like the one below, what changes would need to be made to include tickets that may not have all the fields filled in yet? But I still want those tickets included in the report.

So if my ticket has a value in 'SerialNumber', but not in 'ToteIDName', I'd still like included in the report.

Example:
<Rest of report headers>,'SerialNumber','ToteIDName'
,123456,ID01
,234567,ID01
,345678,

Select Distinct Top 1000000 htblticket.ticketid,
  '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
  htblticket.date,
  htbltickettypes.typename,
  htblticketstates.statename,
  htblticket.subject,
  htblusers1.name As [User Name],
  htblusers.name As [Help Desk Agent Name],
  SerialNumber.data As SerialNumber,
  ToteIDName.data As ToteIDName
From htblticket
  Left Join htblagents On htblagents.agentid = htblticket.agentid
  Left Join htblusers On htblusers.userid = htblagents.userid
  Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
  Left Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htbltickettypes On htbltickettypes.tickettypeid =
    htblticket.tickettypeid
  Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
    htblticketcustomfield.data
  From htblticketcustomfield
    Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
  Where htblcustomfields.name Like 'PCD-HDD Serial Number') As SerialNumber
    On htblticket.ticketid = SerialNumber.ticketid
  Inner Join (Select Top 1000000 htblticketcustomfield.ticketid,
    htblticketcustomfield.data
  From htblticketcustomfield
    Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
  Where htblcustomfields.name Like 'PCD-HDD ToteID Name') As ToteIDName
    On htblticket.ticketid = ToteIDName.ticketid

[David_G · Accepted solution]

You will have to Left Join the subqueries you have created to list the ticket custom fields. With an Inner Join, you will only be able to see tickets where a value is found for your ticket custom field. For your convenience, I have added the adjusted version with Left Joins below.

Select Distinct Top 1000000 htblticket.ticketid,
  '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
  htblticket.date,
  htbltickettypes.typename,
  htblticketstates.statename,
  htblticket.subject,
  htblusers1.name As [User Name],
  htblusers.name As [Help Desk Agent Name],
  SerialNumber.data As SerialNumber,
  ToteIDName.data As ToteIDName
From htblticket
  Left Join htblagents On htblagents.agentid = htblticket.agentid
  Left Join htblusers On htblusers.userid = htblagents.userid
  Inner Join htblusers htblusers1 On htblusers1.userid = htblticket.fromuserid
  Left Join htblticketstates On htblticketstates.ticketstateid =
    htblticket.ticketstateid
  Inner Join htbltickettypes On htbltickettypes.tickettypeid =
    htblticket.tickettypeid
  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
    htblticketcustomfield.data
  From htblticketcustomfield
    Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
  Where htblcustomfields.name Like 'PCD-HDD Serial Number') As SerialNumber
    On htblticket.ticketid = SerialNumber.ticketid
  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
    htblticketcustomfield.data
  From htblticketcustomfield
    Inner Join htblcustomfields On htblcustomfields.fieldid =
      htblticketcustomfield.fieldid
  Where htblcustomfields.name Like 'PCD-HDD ToteID Name') As ToteIDName
    On htblticket.ticketid = ToteIDName.ticketid