use foreach or single query? (Counting Inventory)

[eluser]seanloving[/eluser]
I have a products table (product_id, part_number, description, price, etc)

I have an inventory table (inventory_id, product_id, serial_number, etc)

For each product in my products table I want to count the number of times the product_id appears on the inventory table.

Basically, I want to retrieve the entire products table, but with a 'quantity' field added to the results showing the count of each product in inventory.

I can show my code, but this is basically a procedural question. Right now I am using a foreach loop and it works just fine. However, I was wondering if there is a way to perform this operation as a SINGLE query?

Thanks!

SL

[eluser]Unknown[/eluser]
Try this:

Code:

SELECT
  inv.product_id,
  COUNT(*) AS product_quantity,
  prd.part_number,
  prd.description,
FROM inventory AS inv
JOIN products AS prd
ON prd.product_id = inv.product_id
GROUP BY inv.product_id

[eluser]seanloving[/eluser]
[quote author="pete01" date="1264188945"]Try this:

Code:

SELECT
  inv.product_id,
  COUNT(*) AS product_quantity,
  prd.part_number,
  prd.description,
FROM inventory AS inv
JOIN products AS prd
ON prd.product_id = inv.product_id
GROUP BY inv.product_id

[/quote]
Thanks pete01 for the fast reply. This looks about like what I have, but without the foreach - perfect (hopefully). I'll try it and let you know how it works. --Sean Loving

[eluser]seanloving[/eluser]
[quote author="pete01" date="1264188945"]Try this:[/quote]

Now that I understand a couple more basics (thanks @pete01) I have this nice little function:

Code:

function get_inventory($location)
    {
        // for a given $location, return the product list showing product inventory levels
        return $this->CI->db
                        ->select('count(*) as qty,part_number,description,products.product_id')
                        ->from('inventory')
                        ->join('locations', 'locations.location_id=inventory.location_id')
                        ->where('location_name',$location)
                        ->join('products', 'products.product_id=inventory.product_id')
                        ->group_by('products.product_id')
                        ->get()->result_array();
    }

[eluser]seanloving[/eluser]
Now I'm trying to figure out how to return the entire product list, including the products for which there is none in inventory. Right now the function only returns product records (including quantity column) for which there is at least one item in the inventory table.

I wonder how to make it return the entire product list?

[eluser]JoostV[/eluser]

Code:

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

[eluser]seanloving[/eluser]
[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

[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)

[eluser]seanloving[/eluser]
[quote author="noctrum" date="1264206008"]
Just saw that you have two joins... You would need to left join inventory and locations to products[/quote]

I have tried using 'left' on both of my joins above (thanks @noctrum ;-) ) but still I get no results for the products that are not in inventory (i.e. a product whose product_id does not identify at least one record on the inventory table).

[quote author="noctrum" date="1264206008"] (qty value will probably need to be changed to something like "count(inventory.*) AS qty" as well)[/quote]

That syntax no work, but similar did not work either. RANT: I spent too much time on this what should be simple.:long:

Alas, I'm still trying to display my product list with a quantity column that properly shows qty=0.

What I want is this:
- If a particular product on my product list does not appear on the inventory table, then that product should appear on my query results and its quantity should be 0.

What I have is this:
- That product is not appearing on my query results.



SL

[eluser]danmontgomery[/eluser]
Can you post the actual SQL being run by enabling profiler?

Also, you can use IFNULL to display 0 instead of NULL

Code:

SELECT IFNULL(qty, 0) AS qty