Advanced Search

Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Include column from neighboring table only on condition (MySQL)

  1. #1
    Join Date
    Jan 2008
    Posts
    4,158
    Thanks
    28
    Thanked 623 Times in 619 Posts
    Blog Entries
    1

    Default Include column from neighboring table only on condition (MySQL)

    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:
    Code:
    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).

    Code:
    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
    Last edited by Nile; 08-12-2013 at 04:36 AM.

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,623
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    ...maybe? untested.
    Code:
    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 = '%')
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  3. #3
    Join Date
    Jan 2008
    Posts
    4,158
    Thanks
    28
    Thanked 623 Times in 619 Posts
    Blog Entries
    1

    Default

    @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.
    Last edited by Nile; 08-11-2013 at 06:06 AM.

  4. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,623
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by Nile View Post
    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
    Code:
    ((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.
    Last edited by traq; 08-11-2013 at 06:46 AM.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  5. The Following User Says Thank You to traq For This Useful Post:

    Nile (08-12-2013)

  6. #5
    Join Date
    Jan 2008
    Posts
    4,158
    Thanks
    28
    Thanked 623 Times in 619 Posts
    Blog Entries
    1

    Default

    @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
    Last edited by Nile; 08-11-2013 at 03:36 PM.

  7. #6
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,623
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by Nile View Post
    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?

    Code:
    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.

    Quote Originally Posted by Nile View Post
    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.
    Last edited by traq; 08-11-2013 at 08:34 PM.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  8. The Following User Says Thank You to traq For This Useful Post:

    Nile (08-12-2013)

  9. #7
    Join Date
    Jan 2008
    Posts
    4,158
    Thanks
    28
    Thanked 623 Times in 619 Posts
    Blog Entries
    1

    Default

    Quote Originally Posted by traq View Post
    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.

  10. #8
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,623
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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?
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  11. The Following User Says Thank You to traq For This Useful Post:

    Nile (08-12-2013)

  12. #9
    Join Date
    Jan 2008
    Posts
    4,158
    Thanks
    28
    Thanked 623 Times in 619 Posts
    Blog Entries
    1

    Default

    Quote Originally Posted by traq View Post
    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."

  13. #10
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,623
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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.

    Code:
    SELECT * FROM Users
    Code:
    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:
    Code:
    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.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  14. The Following User Says Thank You to traq For This Useful Post:

    Nile (08-12-2013)

Similar Threads

  1. how to add another column on this table....
    By lilyoungfella in forum HTML
    Replies: 2
    Last Post: 05-25-2009, 12:45 PM
  2. php table INCLUDE
    By Anexxion in forum PHP
    Replies: 4
    Last Post: 04-14-2008, 10:27 PM
  3. how to do create,insert, select, delete temp table for this condition
    By giselle2008 in forum MySQL and other databases
    Replies: 0
    Last Post: 01-15-2008, 06:53 AM
  4. Help me to scroll the table column
    By dak in forum Dynamic Drive scripts help
    Replies: 0
    Last Post: 10-28-2006, 11:39 AM
  5. include external css based on condition
    By jigarshah in forum JavaScript
    Replies: 11
    Last Post: 10-10-2006, 09:35 PM

Tags for this Thread

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
  •