SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple counts in a query?

    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:
    Code MySQL:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dbemowsk View Post
    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.
    yes, you can

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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'
         , COALESCE(at.assets,0) AS assets
         , COALESCE(em.models,0) AS models
      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
    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 
    ORDER 
        BY inventory_id ASC
    LIMIT 0,20
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the OUTER keyword is optional, i just prefer to write it every time, to emphasize that it's an outer join

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •