SELECT
  AP.AppointmentID,
  AT.IsService,
  COALESCE(BP.ClientProductID, -1) AS ClientProductID,
  AP.ClientBranchID,
  AP.ClientID,
  COALESCE(AP.ProductID, 14) AS ProductID,
  AP.AttendanceTime,
  AP.Duration,
  COALESCE(AP.ConsultantID, 0) AS ConsultantID,
  AT.AttendanceTypeID,
  AP.CalendarID,
  AP.OutlookCalendar,
  AP.OutlookEventID,
  CB.BranchName,
  CB.MapReference
FROM
  tbAppointments AS AP
    LEFT JOIN tbAppointmentTypes AS AT ON (AT.AttendanceTypeID = AP.AttendanceTypeID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = AP.ClientBranchID)
    LEFT JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = AP.ClientBranchProductID)
WHERE
  (AP.AppointmentID = @nAppointmentID)
