php - MySQL Group By removes single results? -


i have 3 tables joining together. works great if data present in 3 tables, in case, return 3 of each row due how joining works assume.

so, in order rid of duplicates, group name, leaving 1 of each unique row left, great.

however, if third table empty returns no rows @ all. if remove group part, returns 1 of each row.

so question 2-fold: 1. why happening? 2. how fix it?

in short, question is: "why mysql remove rows if 1 of each found, when using group by".

for instance if these names back: car phone tv

and go group name, returns nothing @ all. if this:

car phone tv car phone tv 

the group works expected , leaves me 1 of each.

here sql (with group-by @ end):

select name, amount, amount_left `template_useages_products` join products on template_useages_products.product_id = products.id  join leftovers on leftovers.template_useages_id = template_useages_products.template_useages_id template_useages_products.template_useages_id = ? group name 

thanks in advance.

select     name, amount, amount_left     template_useages_products     left join products on template_useages_products.product_id = products.id     left join leftovers on leftovers.template_useages_id = template_useages_products.template_useages_id     template_useages_products.template_useages_id = ? group     name 

use left join instead. join refers inner join default in mysql, means results excluded if of tables lacks matching record. ref

by using left join, columns in final result table filled null.


Comments