| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-15-03, 22:02
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 10
|
|
|
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 22:09.
|
10-15-03, 22:35
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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
|
|
10-15-03, 22:37
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 10
|
|
|
|
Quote:
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.
|
|
10-15-03, 23:25
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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
|
|
10-15-03, 23:29
|
|
Registered User
|
|
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-15-03 at 23:31.
|
10-15-03, 23:31
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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
|
|
10-15-03, 23:33
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 10
|
|
Quote:
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?
|
|
10-16-03, 07:37
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
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
|
|
10-16-03, 12:35
|
|
Registered User
|
|
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-16-03, 12:50
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
the samples help, sorta
did you try my last query?
|
|
10-16-03, 13:42
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 10
|
|
Quote:
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.
|
|
10-16-03, 13:50
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
okay, right
change WHERE to AND -- it is positioned in the right place to be an additional condition on the correct join
|
|
10-16-03, 13:55
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 10
|
|
Quote:
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!
|
|
10-16-03, 18:16
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
|
|
thanks
although not so mighty that it took me three tries to figure it out
thanks again
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Query Help
