Results 1 to 14 of 14

Thread: Query Help

  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: Query Help

    I have three tables I'm using a generate a list of point data associated with a particular group.

    However, the data in the points table is assigned to smaller "zones" which are stored in a zones table and then each zone is assigned to a group, sometimes multiple zones to one group.

    The query I have so far is:

    Code:
    SELECT g.group_id, g.group_name, SUM(p.point_value) AS points
                                FROM ups_points p, ups_zone z, ups_zonegroups g
                                WHERE p.point_playerid=XX
                                AND p.point_zoneid=z.zone_id
                                AND g.group_id=z.zone_groupid
                                GROUP BY g.group_id
                                ORDER BY g.group_name
    The problem is, I need to pull ALL the groups from the group table, not just the ones that have references to zones in that group in the points table.

    If there are no entries in the points table for zones associated with that group, I need to display a 0 for the sum not just skip it entirely like it's doing now.

    I can post more data and table layouts if needed, just trying to keep this brief.

    Thanks in advance!

    -Steve

    EDIT:

    Here are the descriptions for each table:

    Code:
    ups_points
    +-----------------+----------------------+------+-----+---------------------+----------------+
    | Field           | Type                 | Null | Key | Default             | Extra          |
    +-----------------+----------------------+------+-----+---------------------+----------------+
    | point_id        | int(11) unsigned     |      | PRI | NULL                | auto_increment |
    | point_playerid  | int(11) unsigned     |      | MUL | 0                   |                |
    | point_value     | smallint(5)          |      |     | 0                   |                |
    | point_runid     | int(11) unsigned     |      | MUL | 0                   |                |
    | point_zoneid    | smallint(3) unsigned |      | MUL | 0                   |                |
    | point_eqid      | int(11) unsigned     |      | MUL | 0                   |                |
    | point_timestamp | datetime             |      |     | 0000-00-00 00:00:00 |                |
    +-----------------+----------------------+------+-----+---------------------+----------------+
    
    ups_zone:
    +------------------+----------------------+------+-----+---------+----------------+
    | Field            | Type                 | Null | Key | Default | Extra          |
    +------------------+----------------------+------+-----+---------+----------------+
    | zone_id          | smallint(4) unsigned |      | PRI | NULL    | auto_increment |
    | zone_groupid     | smallint(4) unsigned |      | MUL | 0       |                |
    | zone_name        | varchar(125)         |      |     |         |                |
    | zone_points      | smallint(4)          |      |     | 0       |                |
    | zone_max_players | smallint(3)          |      |     | 9       |                |
    +------------------+----------------------+------+-----+---------+----------------+
    
    ups_zonegroups:
    +---------------+----------------------+------+-----+---------+----------------+
    | Field         | Type                 | Null | Key | Default | Extra          |
    +---------------+----------------------+------+-----+---------+----------------+
    | group_id      | smallint(4) unsigned |      | PRI | NULL    | auto_increment |
    | group_name    | varchar(125)         |      |     |         |                |
    | group_percent | smallint(4) unsigned |      |     | 0       |                |
    +---------------+----------------------+------+-----+---------+----------------+
    Last edited by jaofos; 10-15-03 at 23:09.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select g.group_id
         , g.group_name
         , sum(p.point_value) as points
      from ups_points p
    left outer
      join ups_zone z
        on p.point_zoneid=z.zone_id
    left outer
      join ups_zonegroups g
        on z.zone_groupid = g.group_id
     where p.point_playerid = XX 
    group
        by g.group_id
         , g.group_name
    order
        by g.group_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    10
    Originally posted by r937
    Code:
    select g.group_id
         , g.group_name
         , sum(p.point_value) as points
      from ups_points p
    left outer
    
      join ups_zone z
        on p.point_zoneid=z.zone_id
    left outer
      join ups_zonegroups g
        on z.zone_groupid = g.group_id
     where p.point_playerid = XX 
    group
        by g.group_id
         , g.group_name
    order
        by g.group_name
    That returns the exact same result set as my query.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh yes, i see why

    i was misled by your condition of searching for a particular player

    so, do you really want all groups?
    Code:
    select g.group_id
         , g.group_name
         , sum(p.point_value) as points
      from ups_zonegroups g
    left outer
      join ups_zone z
        on g.group_id = z.zone_groupid 
    left outer
      join ups_points p
        on z.zone_id = p.point_zoneid
    group
        by g.group_id
         , g.group_name
    order
        by g.group_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2003
    Posts
    10
    Well, see, I do need to search by a particular player.

    This query generates a list of zones and the sum of all the users points for that zone.

    So I need to list all groups, even the ones with no point entries and have the ones that have no point entries set to 0 points.

    The problem that I'm seeing is that it's requiring the point_zoneid in the points table to refer back to the group_id, therefore if the user has no point entries for a particular zone (which is VERY) it never gets referred back to that group to pull the name and id.
    Last edited by jaofos; 10-16-03 at 00:31.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so how do you plan to combine a search for a particular player with a list of all groups? can the player belong to more than one group?

    your table layouts were nice but there was no indication of the one-to-many caridnalities of the relationships involved
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2003
    Posts
    10
    Originally posted by r937
    so how do you plan to combine a search for a particular player with a list of all groups? can the player belong to more than one group?

    your table layouts were nice but there was no indication of the one-to-many caridnalities of the relationships involved
    The "player" doesn't belong to a group, the "zones" belong to a group.

    The player earns points for particular zones, and there can be more than one zone per group.

    Would some sample data help?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sample data might help, but i will simply assume a player can play in multiple zones

    just add a WHERE clause to the last query i gave

    the results will then be all groups, where each one shows the points that this player accumulated


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2003
    Posts
    10
    Here's some sample data and a brief explanation of how the system works.

    This is point tracking system for running zones in a MUD. Players "run" each "zone" and earn points based on that run. When players "pick" equipment from that zone, points are taken by inserting a row with a negative point value assigned to that player and that zone_id.

    Zones are grouped together in the "groups" because some of the larger zones are divided into "days" that you can run at different intervals, earn different equipment and give out different amount of points, yet we want all points and equipment earned to fall into that zone as a whole, hence the "group".

    Hope this explanation gives you a better idea of whats going on and what the query should return. What I'm looking for is getting everything in the ups_zonegroups table returned in order by group_name, and if there are no points in the ups_points table associated with zones from that group, I need a 0 for the sum.

    Code:
    select * from ups_zone
    
    +---------+--------------+-------------------------------+-------------+------------------+
    | zone_id | zone_groupid | zone_name                     | zone_points | zone_max_players |
    +---------+--------------+-------------------------------+-------------+------------------+
    |       1 |            1 | Elysium Day 1                 |          22 |               10 |
    |       2 |            1 | Elysium Day 2                 |          26 |               10 |
    |       3 |            2 | Bloodstone Day 1              |          15 |               10 |
    |       4 |            2 | Bloodstone Day 2              |          22 |               10 |
    |       5 |            2 | Bloodstone Day 3              |          14 |               10 |
    |       6 |            2 | Bloodstone Day 4              |          23 |               10 |
    |       7 |            2 | Bloodstone DK                 |          10 |               10 |
    |       8 |            2 | Bloodstone Glab               |          10 |               10 |
    |       9 |            3 | Tomb of Oblivion Day 1        |          10 |                9 |
    |      10 |            3 | Tomb of Oblivion Bronze Day 2 |          14 |                9 |
    |      11 |            3 | Tomb of Oblivion Silver Day 2 |          16 |                9 |
    |      12 |            7 | Lyryranoth                    |          15 |                9 |
    |      13 |            8 | Den of the Necromancer        |          12 |                9 |
    |      14 |            4 | Demonforge Day 1              |          20 |                9 |
    |      15 |            9 | Coliseum                      |          17 |                9 |
    |      16 |            3 | Tomb of Oblivion Gold Day 2   |          17 |                9 |
    |      29 |            6 | Kalata                        |          19 |                9 |
    |      20 |            4 | Demonforge Day 1.5            |          13 |                9 |
    |      31 |           17 | The Tower of Reclasta         |          15 |                9 |
    |      22 |            2 | Bloodstone Lloth              |           7 |                9 |
    |      23 |            5 | Barbegazian Alps              |          30 |                9 |
    |      24 |           10 | Eldricks                      |          11 |                9 |
    |      25 |           11 | Van'Kyln's Estate     |          19 |                9 |
    |      26 |           12 | OldVeri                       |          14 |                9 |
    |      27 |           13 | Condemned Arena               |          13 |                9 |
    |      28 |           14 | Chepstow                      |          11 |                9 |
    |      30 |           16 | Old Points                    |           0 |                9 |
    |      32 |           12 | Verigaard                     |          10 |                9 |
    +---------+--------------+-------------------------------+-------------+------------------+
    
    select * from ups_zonegroups
    +----------+---------------------------+---------------+
    | group_id | group_name                | group_percent |
    +----------+---------------------------+---------------+
    |        1 | Elysium                   |            50 |
    |        2 | Bloodstone                |            60 |
    |        3 | Tomb of Oblivion          |            40 |
    |        4 | Demonforge                |            20 |
    |        5 | Barbegazian Alps          |            20 |
    |        6 | Kalata                    |            35 |
    |        7 | Lyryranoth                |            20 |
    |        8 | Den of the Necromancer    |            10 |
    |        9 | Coliseum                  |            40 |
    |       10 | Eldricks                  |            20 |
    |       11 | Van'Kyln's Estate |            20 |
    |       12 | Verigaard                 |            20 |
    |       13 | Condemned Arena           |            20 |
    |       14 | Chepstow                  |            20 |
    |       16 | Old Points                |             1 |
    |       17 | The Tower of Reclasta     |            20 |
    +----------+---------------------------+---------------+
    
    select * from ups_points where point_playerid=1 limit 30,15;
    +----------+----------------+-------------+-------------+--------------+------------+---------------------+
    | point_id | point_playerid | point_value | point_runid | point_zoneid | point_eqid | point_timestamp     |
    +----------+----------------+-------------+-------------+--------------+------------+---------------------+
    |     1929 |              1 |          13 |         157 |            9 |          0 | 2003-05-10 03:57:47 |
    |     2030 |              1 |          16 |         164 |           26 |          0 | 2003-05-10 20:25:21 |
    |     2031 |              1 |          -8 |         164 |           26 |       1037 | 2003-05-10 20:28:19 |
    |     2036 |              1 |          18 |         165 |           14 |          0 | 2003-05-10 22:28:23 |
    |     2050 |              1 |          26 |         166 |            2 |          0 | 2003-05-11 00:50:58 |
    |     2069 |              1 |          11 |         167 |           24 |          0 | 2003-05-11 04:39:02 |
    |     2081 |              1 |          12 |         168 |           13 |          0 | 2003-05-11 05:14:34 |
    |     2167 |              1 |          13 |         175 |            9 |          0 | 2003-05-11 18:51:23 |
    |     2183 |              1 |          20 |         176 |           11 |          0 | 2003-05-11 20:18:59 |
    |     2195 |              1 |          18 |         177 |           16 |          0 | 2003-05-11 21:32:45 |
    |     2204 |              1 |          -2 |         177 |            9 |       1134 | 2003-05-11 22:46:05 |
    |     2217 |              1 |          19 |         179 |           25 |          0 | 2003-05-12 00:07:00 |
    |     2238 |              1 |           6 |         180 |           13 |          0 | 2003-05-12 00:16:29 |
    |     2324 |              1 |         -50 |         154 |            1 |        968 | 2003-05-12 23:47:16 |
    |     2325 |              1 |         -50 |         154 |            9 |        968 | 2003-05-12 23:47:16 |
    +----------+----------------+-------------+-------------+--------------+------------+---------------------+

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the samples help, sorta

    did you try my last query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2003
    Posts
    10
    Originally posted by r937
    the samples help, sorta

    did you try my last query?
    mysql> SELECT g.group_id, g.group_name, sum( p.point_value ) AS points
    -> FROM ups_zonegroups g
    -> LEFT OUTER
    -> JOIN ups_zone z ON g.group_id = z.zone_groupid
    -> LEFT OUTER
    -> JOIN ups_points p ON z.zone_id = p.point_zoneid
    -> WHERE p.point_playerid = 1
    -> GROUP
    -> BY g.group_id, g.group_name
    -> ORDER
    -> BY g.group_name;
    +----------+---------------------------+--------+
    | group_id | group_name | points |
    +----------+---------------------------+--------+
    | 5 | Barbegazian Alps | 22 |
    | 2 | Bloodstone | 84 |
    | 9 | Coliseum | 0 |
    | 4 | Demonforge | 18 |
    | 8 | Den of the Necromancer | 4 |
    | 10 | Eldricks | 0 |
    | 1 | Elysium | 16 |
    | 6 | Kalata | 0 |
    | 7 | Lyryranoth | 5 |
    | 16 | Old Points | 0 |
    | 17 | The Tower of Reclasta | 0 |
    | 3 | Tomb of Oblivion | 60 |
    | 11 | Van'Kyln's Estate | 1 |
    | 12 | Verigaard | 6 |
    +----------+---------------------------+--------+

    As you can see, since there are no entries for zones 27 and 28 in the ups_points table where point_playerid=1, those two groups are not displayed.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, right

    change WHERE to AND -- it is positioned in the right place to be an additional condition on the correct join

  13. #13
    Join Date
    Oct 2003
    Posts
    10
    Originally posted by r937
    okay, right

    change WHERE to AND -- it is positioned in the right place to be an additional condition on the correct join
    YES!

    Thank you oh mighty SQL god!

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks

    although not so mighty that it took me three tries to figure it out

    thanks again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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