I've got a parent table of trainings and a child table of trainingattendees.
That is a training can be attended by multiple attendees.
I have a bit field in my trainingattendee table called Deleted
that has a default value of (0). That is the record is not deleted. This value can change to 1 if the record is deleted.
I was simply doing a left join from trainings to trainingattendees to get some data but if ALL my training attendees table rows are marked as deleted (1) I don't get my training record (the left side table)
That is this works:
SELECT
Training.TrainingID AS TrainingID
FROM
Training
LEFT OUTER JOIN
TrainingAttendee ta
ON
ta.TrainingID=Training.TrainingID
WHERE
LOWER(Training.Title) = LOWER('Courtesy Dust Mask Training')
AND DATEADD(d, DATEDIFF(d, 0, Training.StartDate), 0) >= DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
AND (ta.TrainingAttendeeStatusID=4)
GROUP BY
Training.TrainingID
The issue here is this works because I am not filtering out my training attendee records for deleted ones. I have created a record in my training attendee table but marked it as deleted and reran this same query with one additional where condition.
SELECT
Training.TrainingID AS TrainingID
FROM
Training
LEFT OUTER JOIN
TrainingAttendee ta
ON
ta.TrainingID=Training.TrainingID
WHERE
LOWER(Training.Title) = LOWER('Courtesy Dust Mask Training')
AND DATEADD(d, DATEDIFF(d, 0, Training.StartDate), 0) >= DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
AND (ta.Deleted=0)
AND (ta.TrainingAttendeeStatusID=4)
GROUP BY
Training.TrainingID
By adding this: AND (ta.Deleted=0)
I then thought I could change this condition to this:
AND (ta.Deleted=0 OR ta.Deleted IS NULL)
But this too did not work...How can I properly do this such that I get my "left" table data ?