Left Join Does Not Return All The Records From The Left Side Table

SELECT d.mt_code, d.dep_name, d.service_name, COUNT(*) FROM DepartmentService AS d LEFT JOIN tbl_outgoing AS t ON d.mt_code = t.depCode WHERE d.service_type =

Solution 1:

The problem could be that you are filtering on the joined table using the where condition which will filter also the department services which don"t have a match in the join, move the filtering in the join and leave only the filters on d in the where clause:

SELECT d.mt_code,
   d.dep_name,
   d.service_name,
   COUNT(t.id)
FROM DepartmentService AS d
LEFTJOIN tbl_outgoing AS t 
  ON d.mt_code = t.depCode 
    AND t.smsc = "mobitelMT"
    AND t.sendDate BETWEEN'2014-07-01'AND'2014-07-02'WHERE d.service_type ='MT'GROUPBY d.mt_code

---

To explain why this happens I'll walk you through what happens with your query and with my query, as dataset I'll use this:

states
 _____________ 
| id | state   |
|  1 | Germany |
|  2 | Italy   |
|  3 | Sweden  |
|____|_________|

cities

 _______________________ ____________
| id | city   | state_fk  | population |
|  1 | Berlin |        1  |         10 |
|  2 | Milan  |        2  |          5 |
|____|________|___________|____________|

First I'll go through your query.

SELECT s.id, s.state, c.population, c.city
FROM states s
LEFTJOIN cities c
ON c.state_fk = s.id
WHERE c.population <10

So le't go step by step, you select the three states, left join with cities ending up with:

_____________ ____________________
| id | state   | population | city   |
|  1 | Germany |         10 | Berlin |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|

The you filter the population using WHERE c.population < 10, at this point your left with this:

_____________ ____________________
| id | state   | population | city   |
|  2 | Italy   |          5 | Milan  |
|____|_________|____________|________|

You loose Germany because Berlin population was 10 but you lost also Sweden which had NULL, if you wanted to keep the nulls you should have specified it in the query:

WHERE (c.population <10ORISNULL c.population)

Which returns:

_____________ ____________________
| id | state   | population | city   |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|

---

Now my query:

SELECT s.id, s.state, c.population, c.city
FROM states s
LEFTJOIN cities c
ON c.state_fk = s.id
  AND c.population <10

Before joining the two, we filter the table cities (using the AND c.population < 10 condition after the ON), what remains is:

_______________________ ____________
| id | city   | state_fk  | population |
|  2 | Milan  |        2  |          5 |
|____|________|___________|____________|

Because Milan is the only city with population minor than 10, now we can join the two tables:

_____________ ____________________
| id | state   | population | city   |
|  1 | Germany |       NULL | NULL   |
|  2 | Italy   |          5 | Milan  |
|  3 | Sweden  |       NULL | NULL   |
|____|_________|____________|________|

As you can see the data from the left table stays because the filtering condition was applied only to the cities table.

---

The result set changes depending on what you want to achieve, if for example you do want to filter Germany because Berlin has population minor than 10 and keep Sweden you should use the first approach adding the IS NULL condition, if you want to keep it instead, you should use the second approach and pre filter the table on the right of the left join.