Multiple counts in a query?

Databases
1

Okay, here is my dilema

I currently have an inventory table that I am creating a list from using a number of joins. This is the query that I use:

SELECT `inv`.inventory_id AS 'inventory_id', 
       `inv`.media_tag AS 'media_tag', 
       `inv`.asset_tag AS 'asset_tag', 
       `inv`.idea_tag AS 'idea_tag', 
       `eqTyp`.equipment_type AS 'equipment_type', 
       `inv`.equip_make AS 'equip_make', 
       `inv`.equip_model AS 'equip_model', 
       `inv`.equip_serial AS 'equip_serial', 
       `inv`.sales_order AS 'sales_order', 
       `vend`.vendor_name AS 'vendor_name', 
       `inv`.purchase_order AS 'purchase_order', 
       `stat`.status AS 'status', 
       `loc`.location_name AS 'location_name', 
       `rooms`.room_number AS 'room_number', 
       `inv`.notes AS 'notes', 
       `inv`.send_to AS 'send_to', 
       `inv`.one_to_one AS 'one_to_one', 
       `entBy`.user_name AS 'user_name', 
       from_unixtime(`inv`.enter_date, '%m/%d/%Y') AS 'enter_date', 
       from_unixtime(`inv`.modified_date, '%m/%d/%Y') AS 'modified_date' 
FROM mod_inventory_data AS `inv` 
LEFT JOIN mod_inventory_equip_types AS `eqTyp` ON `eqTyp`.equip_type_id = `inv`.equip_type_id 
LEFT JOIN mod_vendors_main AS `vend` ON `vend`.vendor_id = `inv`.vendor_id 
LEFT JOIN mod_inventory_status AS `stat` ON `stat`.status_id = `inv`.status_id 
LEFT JOIN mod_locations_data AS `loc` ON `loc`.location_id = `inv`.location_id 
LEFT JOIN mod_locations_rooms AS `rooms` ON `rooms`.room_id = `inv`.room_id 
LEFT JOIN mod_users_data AS `entBy` ON `entBy`.user_id = `inv`.entered_by 
ORDER BY inventory_id ASC
LIMIT 0,20

I then display the result in a table/matrix view. I am adding links to that matrix for each row that will show an icon at the beginning of the row that links me to a list work orders for an inventory item based on different criteria. The firs link shows me work orders that match the asset_tag field, and the second link shows me work orders that match the equip_make. I am currently doing this using PHP code that loops through the results of the query above and then runs separate queries for each row that determines the data needed to create the links. This does work, however it is slow. What I am wondering is if I can do some more joins, counts, and group bys that can give me the two needed counts to create the links. I figure if I can do all the work in one query it might be faster, but I can’t seem to figure out how it would be done.

2

yes, you can :slight_smile:

however, given the multiple tables already being joined, if you introduce any additional tables that are one-to-many, you could mess things up

best to put the COUNTs and GROUP BYs into subqueries

3

The joins in the current query are one to one, but I do understand how things could get messy with a one to many or many to many relationship added to the mix. I will look into subqueries to see what I can do there.

Thanks

4

if you could post the additional tables that need joining, and the join columns, and the columns being counted, i’d be happy to give it a shot…

5

I am trying to do two separate counts. one of them is to count the number of work orders there are that match the inventory asset tag(asset_tag) and the second is the number of work orders that match the equipment model (equip_model). The work order table is named “mod_workorder_data”.

Thanks much for the help on this. I do need to learn more about subqueries. I think they will help me a lot in the application I am working on.

6 
SELECT inv.inventory_id 
     , inv.media_tag 
     , inv.asset_tag 
     , inv.idea_tag 
     , eqTyp.equipment_type 
     , inv.equip_make 
     , inv.equip_model 
     , inv.equip_serial 
     , inv.sales_order 
     , vend.vendor_name 
     , inv.purchase_order 
     , stat.status 
     , loc.location_name 
     , rooms.room_number 
     , inv.notes 
     , inv.send_to 
     , inv.one_to_one 
     , entBy.user_name 
     , from_unixtime(inv.enter_date, '%m/%d/%Y') AS 'enter_date'
     , from_unixtime(inv.modified_date, '%m/%d/%Y') AS 'modified_date'
     , [COLOR="Blue"]COALESCE(at.assets,0) AS assets
     , COALESCE(em.models,0) AS models[/COLOR]
  FROM mod_inventory_data AS inv
LEFT OUTER
  JOIN mod_inventory_equip_types AS eqTyp
    ON eqTyp.equip_type_id = inv.equip_type_id
LEFT OUTER
  JOIN mod_vendors_main AS vend
    ON vend.vendor_id = inv.vendor_id
LEFT OUTER
  JOIN mod_inventory_status AS stat
    ON stat.status_id = inv.status_id
LEFT OUTER
  JOIN mod_locations_data AS loc
    ON loc.location_id = inv.location_id
LEFT OUTER
  JOIN mod_locations_rooms AS rooms
    ON rooms.room_id = inv.room_id
LEFT OUTER
  JOIN mod_users_data AS entBy
    ON entBy.user_id = inv.entered_by
[COLOR="blue"]LEFT OUTER
  JOIN ( SELECT asset_tag
              , COUNT(*) AS assets
           FROM mod_workorder_data
         GROUP
             BY asset_tag ) AS at
    ON at.asset_tag = inv.asset_tag 
LEFT OUTER
  JOIN ( SELECT equip_model
              , COUNT(*) AS models
           FROM mod_workorder_data
         GROUP
             BY equip_model ) AS em
    ON em.equip_model = inv.equip_model[/COLOR] 
ORDER 
    BY inventory_id ASC
LIMIT 0,20
7

I tested the query and it worked slick. One question I do have since I am not an SQL guru by any stretch, what does the LEFT OUTER JOIN do that a LEFT JOIN does not. I noticed that you changed the LEFT JOINs that I had in the original query to the LEFT OUTER JOINs. I plan to pick apart the query that you wrote to get a full understanding of it. I greatly appreciate it though.

8

the OUTER keyword is optional, i just prefer to write it every time, to emphasize that it’s an outer join

:slight_smile:

9

Thanks a bunch again. I am starting to understand the nested subqueries in the joins. I like to make sense of these things before I use them. Understanding is sometimes half the battle.