use foreach or single query? (Counting Inventory)

[eluser]danmontgomery[/eluser]
[quote author="seanloving" date="1264201669"][quote author="JoostV" date="1264199043"]

Code:

->join('products', 'products.product_id=inventory.product_id', 'left')

[/quote]

Thanks @JoostV. I have tried that and several variations. Not happening though. I wonder if I need to focus on the WHERE or the JOIN or something else...

SL[/quote]

[edit]

Just saw that you have two joins... You would need to left join inventory and locations to products:

Code:

return $this->CI->db
                        ->select('count(*) as qty,part_number,description,products.product_id')
                        ->from('products')
                        ->join('inventory', 'products.product_id=inventory.product_id', 'left')
                        ->join('locations', 'locations.location_id=inventory.location_id', 'left')
                        ->where('location_name',$location)
                        ->group_by('products.product_id')
                        ->get()->result_array();

Try that. (qty value will probably need to be changed to something like "count(inventory.*) AS qty" as well)