CODEWITHSUNDEEP
sql
Hitesh
Hitesh

Reputation: 59

Left Join giving wrong result

I am having 3 tables Products, Order, Order_Placed

Products---------- Order---------------- Order_Placed
________________________________________________________
prod_id | -------| order_id | -------| prod_id  |order_id| 

1000----------------2001-------------------1000|2001


1001----------------2002-------------------1000|2002

1002--------------- 2003------------------1001|2001

So my query is something like this:

select P.Prod_ID, COUNT(*) 
from PRODUCTS P
LEFT JOIN ORDER_PLACED OP
ON P.PROD_ID= OP.PROD_ID
group by ....

so result should be:

Prod_Id  Count(*)

1001      2

1002      1

1003      0

Upvotes: 0

Views: 139

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

Nope. count(*) can never return 0. Either it returns nothing (no row at all) or at least 1.

You want:

select P.Prod_ID, COUNT(OP.PROD_ID) 
from PRODUCTS
LEFT JOIN ORDER_PLACED OP
ON P.PROD_ID= OP.PROD_ID
group by ....

NOTE:

There was an attempt to add a qualifier to my response. But count(*) cannot return 0 in any situation with a group by. A zero would mean that there are no rows in the group. And that in turn would mean that there are no rows in the output. When there are no rows selected at all for a global aggregation, then it can return 0.

Upvotes: 2

Related Questions