mysql count brings back at least 1 row

I've recently moved jobs and gone from a working with T-SQL to MySql. So far so good until today.

i'm running the following sql script:

 SELECT PB.idproductbundle AS ID, 
         PB.Name AS Name,
         PB.Discount AS Discount,
         PB.DiscountIsPercent AS DiscountIsPercent,  
         COUNT(PB_P.idproductbundle) AS ProductCount
 FROM `mydb`.productbundles AS PB
 LEFT JOIN `mydb`.ProductBundle_Product PB_P ON PB_P.idproductbundle = PB.idproductbundle

simple command to bring back all product bundles with a count of how many products in that bundle.

Strange thing is, there is currently no data in tables: productbundles or ProductBundle_Product.

but it insits on bringing back 1 row. all the columns are their default value:

ID Name Discount DiscountIsPercent ProductCount

NULL, NULL, NULL, NULL, '0'

In T-Sql this would have no row开发者_开发问答s.

Because you have a COUNT clause in the select, which will bring back a zero if there are no rows that satisfy the query. So you're guaranteed at least one row - the result of the COUNT telling you there are zero rows.

Turns out i was missing a group by :/

 SELECT PB.idproductbundle AS ID, 
         PB.Name AS Name,
         PB.Discount AS Discount,
         PB.DiscountIsPercent AS DiscountIsPercent,  
         COUNT(PB_P.idproductbundle) AS ProductCount
 FROM `ukiss-order-management`.productbundles AS PB
 LEFT JOIN `ukiss-order-management`.ProductBundle_Product PB_P ON PB_P.idproductbundle = PB.idproductbundle
 GROUP BY PB.idproductbundle