SQL Left join not displaying all rows from left?

Your original selection is fine and should allow all records from the services table through. However you're then restricting this by your where clause. If there was no join for a specific row item will have a NULL in it which your WHERE clause is filtering out select services.Name,count(orders.

Service) from services LEFT JOIN orders ON services. Id=orders. Service WHERE item IS NULL OR item IN (1,2,3,4) group by statuses.

Service Forgive me if slightly wrong, I'm coming from SQL Server background.

Your original selection is fine and should allow all records from the services table through. However you're then restricting this by your where clause. If there was no join for a specific row, item will have a NULL in it which your WHERE clause is filtering out.

Select services. Name,count(orders. Service) from services LEFT JOIN orders ON services.Id=orders.

Service WHERE item IS NULL OR item IN (1,2,3,4) group by statuses. Service; Forgive me if slightly wrong, I'm coming from SQL Server background.

1 It would probably be better to move the IN clause into the join condition: ON services. Id = orders. Service AND item IN (...) – a_horse_with_no_name Mar 25 at 14:04 It makes more sense to put it in the join condition, since where item is null ... leads to wrong counts (it also counts items not in (1,2,3,4)) – Frank Schmitt Mar 25 at 14:08 as much as I understand from this wellho.Net/mouth/… example left join in mysql can bring the null values, too.

So writing "is null" is not a requirement I guess. – OkayGuy Mar 25 at 14:10 1 True, but that only holds for the original selection set from the two tables. The WHERE clause is then applied afterwards which will remove them again.

@Frank's solution is cleaner anyway. – Sir Crispalot Mar 25 at 14:13 1 @Emre: In principle, that's correct - a left join brings in all the rows from the services table, regardless of the number of matching rows in the orders table.In this case, however, this doesn't work, since the where condition explicitly removes the rows whose item is not in (1,2,3,4), especially all the rows where item IS NULL. So, if you add "where item IS NULL" to the query as suggested by Sir Crispalot, you get all rows from the services table that have either no order at all or at least one order with item in (1,2,3,4) – Frank Schmitt Mar 25 at 14:20.

First of all, I believe you've got a typo - statuses. Service should be services. Name, right?

Since item is a column from the orders table, you should put it in the join condition: select services. Name,count(orders. Service) from services LEFT JOIN orders ON services.Id=orders.

Service and services. Item IN (1,2,3,4) group by statuses. Service; Otherwise, you filter out the services without orders or whose orders only have items not in (1,2,3,4).

Kind regards, Frank.

Clearly more sensible! – Sir Crispalot Mar 25 at 14:09.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.