[Venthos]

First time posting here, please feel free to smack me upside the head if I perform a faux pas.

However, recently I decided to take up building up a page for my World of Warcraft guild (i've build a few pages for MMORPG guilds prior). I've used them as something to help build and broaden my knowledge, making each one better and better.

With this latest one, my intent was to create a "unified login" and essentially utilize phpBB2's authentication as well as the forum Usergroups to create authentication levels and thus "Member" and "Officer" access.

That part was no sweat, however the next step was creating a Roster page. For this I have built my own table containing my own fields I want to list on this page. One of the fields I have is "phpbb_id" in which I store the phpbb user_id of whom this roster profile entry belongs to.

My original intent was to then query the user_groups table of phpBB2 for a list of all "active" members and officers and only pull the rows from my own table that matched those phpbb_id's.

(the user_group, for those unaware, is just three fields. "user_id", "user_pending", and the "group_id" in which has been given access)

In essence I have this query:

mysql> select * from vantage_roster r, TSBphpbb_user_group g WHERE r.phpbb_id = g.user_id AND (g.group_id = 3 OR g.group_id = 4) ORDER BY name;

group_id 3 = Members, group_id 4 = Officers.
TSBphpbb_user_group = phpBB2's user_group table
vantage_roster = my own table for storing "profiles".

This works as expected with the query, however the problem is that All Officers (group 4's) are ALSO in the usergroup Members (group 3). So Officers appear twice in this query. I *do* want to retain the group_id found in this query so I can parse it into "officer" or "member" on my roster page, however I want group 4's row to take precedence over group 3's row. I was attempting to look for a way to tell the query to not allow duplicate rows of the same "id" (while also providing all other row data), but to no avail.

My question is (hopefully I was concise enough in my description) can what I wish to be done be accomplished through a single Query? Or must I go to multiple queries and or spill over into PHP parsing of the results?

Code:

mysql> select * from vantage_roster r, TSBphpbb_user_group g WHERE r.phpbb_id = g.user_id AND (g.group_id = 3 OR g.group_id = 4) ORDER BY name;
+----+----------+---------+------+-------+-----+-------+-----+-------+-------+-------+------------+-------+------------+-------+----------+-----------+------------+----------+------------+------------+------------+----------+---------+--------------+
| id | phpbb_id | name    | race | class | sex | level | alt | spec1 | spec2 | spec3 | prof1_type | prof1 | prof2_type | prof2 | pvp_rank | attune_mc | attune_bwl | key_ubrs | key_strath | key_scholo | key_onyxia | group_id | user_id | user_pending |
+----+----------+---------+------+-------+-----+-------+-----+-------+-------+-------+------------+-------+------------+-------+----------+-----------+------------+----------+------------+------------+------------+----------+---------+--------------+
|  1 |        2 | Vendak  |    1 |     7 |     |    60 |     |    31 |    20 |     0 |          5 |   300 |         13 |   244 |        3 | 0         |            |          | 0          |            |            |        3 |       2 |            0 |
|  1 |        2 | Vendak  |    1 |     7 |     |    60 |     |    31 |    20 |     0 |          5 |   300 |         13 |   244 |        3 | 0         |            |          | 0          |            |            |        4 |       2 |            0 |
|  2 |        2 | Venx    |    4 |     4 |     |    53 | 0   |    18 |     8 |    18 |          7 |   267 |         13 |   190 |        1 |           |            |          |            |            |            |        3 |       2 |            0 |
|  2 |        2 | Venx    |    4 |     4 |     |    53 | 0   |    18 |     8 |    18 |          7 |   267 |         13 |   190 |        1 |           |            |          |            |            |            |        4 |       2 |            0 |
|  3 |       24 | Zarabet |    3 |     6 | 0   |    56 |     |    15 |     0 |    31 |         15 |   285 |          5 |   285 |        1 |           |            |          |            |            |            |        3 |      24 |            0 |
+----+----------+---------+------+-------+-----+-------+-----+-------+-------+-------+------------+-------+------------+-------+----------+-----------+------------+----------+------------+------------+------------+----------+---------+--------------+
5 rows in set (0.00 sec)

Is returned, but I wish to have only one row returned per "id" of my table, having group_id 4 taking precedence over group_id 3.

Again, my apologies if i'm coming off like an ignorant, but any help or recommendations would be much appreciated.

-Brian

[Venthos]

No recommendations for potential solutions?

My apologies if my post was unclear, I can further clarify if needed. But I would much appreciate any advice even if it's "this won't work, you'll have to find another way".