Include column from neighboring table only on condition (MySQL)

[Nile]

Hey guys, I'm stuck with a query and I don't know where to go with this. I'll start off by talking about the situation that I'm in (so you guys have some context), then I'll go ahead and ask the question.

Basically I'm creating a game that's constantly interacting with the database. In the database, I have tables Users, Games. and UsersGames. This question focuses around the UsersGames table, so it's structure is most important:

mysql> SHOW COLUMNS FROM UsersGames;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| user  | int(11) | NO   |     | NULL    |                |
| game  | int(11) | NO   |     | NULL    |                |
| role  | int(11) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

user and game are both foreign keys to Users and Games respectively.

Now, below I'll post my query, explain it, then explain what I want done (but can't figure out).

SELECT *
FROM Users
WHERE id IN
    (SELECT User
     FROM UsersGames
     WHERE (role = :role
            OR :role = '%')
       AND (game = :game
            OR :game = '%'))

So basically what we're doing here is selecting rows from the Users table where row's ID matches the user column in UsersGames on conditions. The conditions I currently have look to see if the user's role is something specifically or the game their in is something specifically. Notice I also have a :parameter = "%" which allows me to just pass "%" for the parameter if I don't want to filter by the column.

Now, what I'm trying to insert into the query above is another column in the output. Essentially if :game is set to anything else except "%", I want the query to, in addition to selecting Users.* FROM Users, select UsersGames.role (where UsersGames.user = :user AND UsersGames.game = :game). I know I probably have to do something with a union of some sort, I'm just not sure what.

If you guys have any ideas or solutions, please share. I'm reluctant to just letting PHP pull off a couple of simple if statements because that's generally considered bad practice and it'll be most likely quicker and easier to modify for the future in the query regardless. If something is confusing in this question, please don't feel hesitant in asking questions or pointing something out that wasn't quite clear.

Thanks,
Jeremy

[traq]

...maybe? untested.

SELECT {you need to explicitly list all Users columns you want like u.col_name}
FROM Users u
JOIN UsersGames ug
ON u.id = ug.user
WHERE (ug.role = :role OR :role = '%')
AND (ug.game = :game OR :game = '%')

[Nile]

@traq Thanks for taking the time to write your response. The issue with this is that 1) it selects rows from the UsersGames table even when :game is set to "%". The reason this issue is important is because there may be times when :game is set to "%" because the user isn't actually in a game.

Edit: Thinking about it now, it'd be okay for the roles to be null if :game is set to "%", so either null or no role at all is what I'm aiming for.

[traq]

Thinking about it now, it'd be okay for the roles to be null if :game is set to "%", so either null or no role at all is what I'm aiming for.

So, does this work for you?

Or do you need something like

((ug.game = :game OR :game = '%') AND ug.game IS NOT NULL)

to avoid selecting rows where no game is set...?

Edit: btw, I'd change ug.game = :game OR :game = '%' to :game = '%' OR ug.game = :game, because it would skip reading indexes and/or table rows when you don't need to.

[Nile]

@traq No, it doesn't. There are still two (sort of connected) issues I can think of:

1. Like I said above, role should be either empty or nonexistent when :game is a wildcard.
2. When :game is a wildcard, it's returning multiple users (this is connected to the first issue)

Thanks

[traq]

role should be either empty or nonexistent when :game is a wildcard.

So, when :game is a wildcard, you want only rows where role is empty*, correct?

SELECT {list of u.col_names} ,ug.role -- in your OP I missed that you wanted to select the role
FROM Users u
JOIN UsersGames ug
ON u.id = ug.user
WHERE (:game = '%' AND ug.role IS NULL)
OR (ug.game = :game AND (ug.role = :role OR :role = '%'))  -- IIUC

* for consistency, if you haven't already, it would be good choose only one of "empty" (which to me, implies 0 or an empty string) or NULL. In my example above, I'm assuming you use NULL.

When :game is a wildcard, it's returning multiple users (this is connected to the first issue)

...I'm guessing that's because users might be playing more than one game (i.e., be in more than one row in the UsersGroups table)? It might be as simple as using SELECT DISTINCT (provided you're only selecting columns from the Users table).

You might need to GROUP BY instead; I'm not sure. But then, you'd need to determine how MySQL should decide which values to group by.

[Nile]

So, when :game is a wildcard, you want only rows where role is empty*, correct?

No, no, no. Maybe I haven't explained enough. Let me give some situations that I'll be using this in so you can understand better.

When users are in a game, I'm going to select from UsersGames by the game they're in, but along with returning Users.* for each user in the game, I'll also need the row they're in. However, say I want to make a list of every single user. In this case, I won't be selecting by game and therefore role isn't relevant. In fact, in this case, the query shouldn't even need data from UsersGames.

Thanks for all your help.

[traq]

Let's see if I understand correctly:

Given :game = %,
Query should return info from Users table.
Every user should be included once.
Call this "list all users."

Given :game = not-a-wildcard and :role = %,
Query should return same as above, plus the role from UsersGames.
Only users where game = :game should be included.
Call this "list users in game."

Given :game = not-a-wildcard and :role = not-a-wildcard,
Query should return same as above.
Only users where game = :game AND role = :role should be included.
Call this "list users with role in game."

yes/no/kinda?

[Nile]

Given :game = %,
Query should return info from Users table.
Every user should be included once.
Call this "list all users."

Given :game = not-a-wildcard and :role = %,
Query should return same as above, plus the role from UsersGames.
Only users where game = :game should be included.
Call this "list users in game."

Given :game = not-a-wildcard and :role = not-a-wildcard,
Query should return same as above.
Only users where game = :game AND role = :role should be included.
Call this "list users with role in game."

Yes, exactly. There's also an optional change (again, optional... only if it'll make the query easier):

Given :game = %,
Query should return info from Users table, plus a role column set to 0/null.
Every user should be included once.
Call this "list all users."

[traq]

Alright then.

Including the role column with a NULL value on "list all users" would only make it easier (note: "easier," not "prettier" or "more efficient") if you really need to do this in a single query.
If you don't have any particular need, I'd suggest simply using a separate query.

SELECT * FROM Users

SELECT {list of u.col_names} ,ug.role
-- maybe SELECT DISTINCT ? depending on whether users might ever show up in multiple rows for the same game
FROM Users u
JOIN UsersGames ug
ON u.id = ug.user
WHERE (ug.game = :game AND (:role = '%' OR ug.role = :role)

Otherwise, it gets ugly:

SELECT {list of u.col_names} ,IF(:game='%',NULL,ug.role) role
FROM Users u
JOIN UsersGames ug
ON u.id = ug.user
WHERE :game = '%'
OR (
    ug.game = :game
    AND (
        :role = '%'
        OR ug.role = :role
    )
)
GROUP BY {primary key from Users table}

-- I think...  untested.