Programming Forums - MySQL - Basic Combining Of Queries

Programming Forums (http://www.programmingforums.org/forumindex.php)

- PHP (http://www.programmingforums.org/forum29.html)

- - MySQL - Basic Combining Of Queries (http://www.programmingforums.org/showthread.php?t=15469)

Sane	Mar 23rd, 2008 7:13 PM

MySQL - Basic Combining Of Queries

What I need to do is output a table of matches. But at the same time, I need the username of each user_id found in the table.

So I have:

SELECT * FROM $table_match

That fetches every row from the matches table. The matches table has the columns 'user_id1' and 'user_id2'. I want the username for each of these user_ids.

This can be done by calling the following two queries for each row fetched with the above query.

SELECT name FROM $table_user WHERE user_id=$user_id1
SELECT name FROM $table_user WHERE user_id=$user_id2

This works, but the problem with that is it will keep querying for usernames, even if it's already fetched it before. Despite MySQL's cache, I feel that this is slower than it needs to be. Maybe MySQL will optimize the query if I combine the two. Or even better, can I link the user_id column to the name column from the user table?

Thanks in advance.

Seif	Mar 23rd, 2008 8:28 PM

Re: MySQL - Basic Combining Of Queries

not sure I 100% follow what you mean but why not just use an OR statement ?

SELECT name FROM $table_user WHERE user_id=$user_id1 OR user_id=$user_id2

Ideally, if you are constructing a set of results from more than one table that reference each other using foreign keys, you can perform a series of joins to obtain what you need. There are multiple ways of performing a join:

SELECT table_user.name

FROM table_user,table_match

WHERE table_user.userid1 = table_match.userid1 OR

 table_user.userid2 = table_match.userid2

SELECT table_user.name

FROM table_user

INNER JOIN table_match

ON 

 table_user.userid1 = table_match.userid1 OR

 table_user.userid2 = table_match.userid2

Both commands should construct a list of names for each userid. Have a look at JOIN SQL statements. They should provide all that you need.

Sane	Mar 23rd, 2008 8:52 PM

Re: MySQL - Basic Combining Of Queries

I was saying I need the username of every user_id I fetch, on top of the row I originally obtained. I can already do that with the MySQL queries I posted above. I'm now looking for a way to combine the queries so that I don't have to make 1+2*N queries. Instead just 1 query.

Modifying what you posted at the bottom might work. I will try some of that out. Thanks.

Edit: I can do it with one user id. Here's an example of where I need to output a table of ranks, and at the same time get the user name for every user_id.

SELECT $table_ranking.*, $table_user.name 
FROM $table_ranking INNER JOIN $table_user 
ON $table_ranking.user_id=$table_user.user_id AND $table_ranking.game_id=$game_id

The problem comes from extending this to get the usernames of both user_id1 and user_id2 for each row. It seems like I would need multiple joins to accomplish that. Maybe I can try screwing around with nested joins.

Arla	Mar 23rd, 2008 11:42 PM

Re: MySQL - Basic Combining Of Queries

How about

Select a.name as user1, b.name as user2

  from $table_user a

         ,$table_user b

         ,$table_match c

  where a.user_id = c.user_id1

  and     b.user_id = c.user_id2

I think that should do it (bit tired right now, so might have missed something)

Jimbo

Mar 24th, 2008 12:58 AM

Re: MySQL - Basic Combining Of Queries

Not the most elegant, but perhaps fits your needs?

SELECT $table_ranking.*, $table_user.name
FROM $table_ranking INNER JOIN $table_user ON $table_ranking.user_id=$table_user.user_id
WHERE $table_user.user_id IN ($gameIds)

$gameIds should be a CSV list of user IDs put together in your PHP script somewhere (assumption: you're putting a list together in PHP, since this is in the PHP forum...)

Seif	Mar 24th, 2008 7:46 AM

Re: MySQL - Basic Combining Of Queries

Theres no reason why you need multiple queries. if you want every name for user_id1 and user_id2, just add that condition clause to the join. If you are concerned of duplicate name entries in the view from the query, then add a distinct to the statement.

I'm still having a bit of a job visualizing what your problem actually is. are user_id1 and user_id2 unique values? do they both make up a composite key ? it's even more confusing in your second post with differing table and field names.
If you cannot create a view from one query between two tables, then maybe you should reconsider the design of your database for performance gain.

The following single query "should" select a distinct set of names in table user for each user_id and game_id entry in table_ranking.

SELECT DISTINCT $table_ranking.*, $table_user.name 
FROM $table_ranking INNER JOIN $table_user 
ON $table_ranking.user_id=$table_user.user_id OR $table_ranking.user_id=$table_ranking.game_id

Sane	Mar 24th, 2008 9:23 AM

Re: MySQL - Basic Combining Of Queries

@Arla: You got it in one shot. Thanks. Now I'll have to do some testing to see if it is indeed any faster.

@Seif: I didn't think it would be this hard to visualize. I am reporting a table of information. In the table there is a user_id column, but I don't want the user_id of each row. I want the user name for each row.

For example, here: http://codersblock.net/arena/ranking.php. The table "ranking" contains several rows, and a column called "user_id". I report the user name for each row by getting it from a second table. That is shown by the MySQL in my second post.

But now I need to do it where there's a user_id1 AND user_id2. Now, let's go back to my very first post.

Quote:

I want the username for each of these user_ids.

For example, here: http://codersblock.net/arena/lobby.php. The matches table has two user_ids, and I report the user name for "each of these user_ids". This is accomplished by the MySQL from my first post, which I now want to combine into one whole query.

Read my question over again and maybe it will all fall together now. But since I've already got it working with Arla's MySQL, now I only need to optimize it if possible.

Arla	Mar 24th, 2008 5:28 PM

Re: MySQL - Basic Combining Of Queries

Sane,

Glad that was the right query, was a bit hard trying to read between the lines of what you actually wanted, especially when you added the table_ranking into the mix which wasn't even in your first post (and I ignored it, since it wasn't something you mentioned in the original problem).

If possible, post an example of what's in the tables and what you would want as output, since I wasn't sure if you wanted only distinct names as Seif had a query for, or you wanted what I posted, or something else entirely.

All times are GMT -5. The time now is 9:09 PM.