change query to return the rows that don't match

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
  INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
  LEFT OUTER JOIN Clinic_Schedule CS
  开发者_如何学Go  ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
  WHERE CS.schedule_event_source_id = 2

Clinic_Schedule contains 1 record:

clinic_code | schedule_date | schedule_time | section_name | schedule_event_source_id ABC | 2014-03-03 | AM | Faculty | 2

Master_Template contains 168 records:

clinic_code | dw | mo | schedule_time | section_name | section_content

Calendar contains columns to join the Master_Template records with days of the week.

How can I change the query above to return 167 rows? I.e. the total rows in master_template, minus the row in clinic_schedule.

What about this:

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
  INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
  LEFT OUTER JOIN Clinic_Schedule CS
    ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
    AND CS.schedule_event_source_id = 2
  WHERE CS.clinic_code IS NULL

?

To return only the records from T that did NOT have a match in CS, you can use HAVING, which is similar to WHERE but applies the condition on the JOIN's results:

SELECT
  T.clinic_code, C.dt, T.schedule_time, T.section_name,
  T.section_content, CS.schedule_event_source_id
FROM Master_Templates T
INNER JOIN Calendar C
    ON  T.dw = C.dw
    AND T.mo = (C.D - 1) / 7 + 1
    AND C.Y = '2014'
    AND C.M = '3'
    AND T.clinic_code = 'ABC'
LEFT OUTER JOIN Clinic_Schedule CS
    ON  CS.schedule_date=convert(varchar, C.dt, 121)
    AND CS.clinic_code=T.clinic_code
    AND CS.schedule_time=T.schedule_time
    AND CS.section_name = T.section_name
    AND CS.schedule_event_source_id = 2
HAVING CS.clinic_code IS NULL