Soft delete field causes left join not to return the “left” side data

join;

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 ?

Best Answer

(Note: completely updated, when I realized your problems was a bit different than I initially thought).

As you probably realize (given the change you made), if you check for a non-NULL value on a LEFT JOIN column in your WHERE clause, it can make your LEFT JOIN act like an INNER JOIN.

There are two ways to handle this problem:

You can perform all your checks in the JOINs ON clause
You can check for the condition you're interested in within the WHERE clause, but OR that with a condition that is true when there are no rows found from the "right" table.

However, this can have two different effects.

When you check a condition in the ON clause, that becomes a part of the condition for joining the rows from the "right" table to the "left" table. So, if a row fails to meet your condition, it has no impact on the rows from the "left" table - it's as if it doesn't exist.

On the other hand, when you're checking in the WHERE clause, it is already tied to the data form the "left" table. So, if there are rows from the "right" table, but none of them match your condition, the rows from the "left" table do get excluded.

With that in mind, let's take a look at what you're trying to do. You want training classes where the trainees (if any) are in status 4, and you want to ignore any trainees marked as deleted.

So, try this instead:

SELECT
    Training.TrainingID AS TrainingID
FROM 
    Training 
LEFT OUTER JOIN
    TrainingAttendee ta
ON
    (ta.TrainingID=Training.TrainingID AND ta.Deleted = 0)
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 OR ta.TrainingID IS NULL)
GROUP BY
    Training.TrainingID

This should treat any ta records marked as deleted as if they don't exist, but will only return Training records is they have no attendees, or if they have non-deleted attendees in the desired status.

NOTE: This does change your original query (which you said was working correctly), which was basically acting as an INNER JOIN. Try the new query with and without the OR ta.TrainingID IS NULL in the status check, to see if you really if the results are different, and which results you really wanted.

UPDATE: Further discussion with JonH revealed that he was trying to get a count of the number of seats in each class (where, logically enough, deleted attendees shouldn't be counted). He'd added the following to his query's SELECT list:

   ,ISNULL(COUNT(*),0) AS TakenSeats

However, he was still getting back 1 seat as taken, even if all attendees had been deleted. I explained that this was due to the fact that COUNT(*) was counting the number of rows returned - and even if no TrainingAttendee row was found, you'd still get one row from Training. I recommended changing this to:

   ,ISNULL(COUNT(ta.TrainingID),0) AS TakenSeats

which (since COUNT ignores NULL values) should return 0 when there are no TrainingAttendee rows found for a given Training row.

Related Solutions

Mysql – Finding Duplicates in LEFT JOIN

This may appear extrememly convoluted by here is the query you need

select * from
(select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
left join
(select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);

I loaded you sample data and I got this:

mysql> select * from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+----------+------------------------+----------+----------+-----------+
| dupcount | emp_name | dept_name              | dupcount | emp_name | dept_name |
+----------+----------+------------------------+----------+----------+-----------+
|        2 | amar     | finance                |        1 | amar     | finance   |
|        1 | anthony  | information_technology |     NULL | NULL     | NULL      |
+----------+----------+------------------------+----------+----------+-----------+
2 rows in set (0.02 sec)

mysql>

Here is it without the dupcount values

mysql> select a.emp_name,a.dept_name,b.emp_name,b.dept_name from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| amar     | finance                | amar     | finance   |
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

Give it a Try !!!

MySQL: LEFT OUTER JOIN within reason

First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:

SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid

Now you can build a join to retrieve not only sid, but all of the data of tablethree:

SELECT a.*
FROM tablethree a
INNER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate

This is the result set you actually want to "left join in". You have to insert this into your original query:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN (
  SELECT a.*
  FROM tablethree a
  INNER JOIN (
    SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
  ) b
  ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate

What should also work is the following:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
LEFT OUTER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
  ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate

WHERE t1.fieldone = 1 
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
  AND mostrecent.enddate IS NULL

This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.

Best Answer

Related Solutions

Mysql – Finding Duplicates in LEFT JOIN

MySQL: LEFT OUTER JOIN within reason

Related Question