[RESOLVED] MySQL: Max(), Count(), and foreign keys

[Ryodox]

Here it is: I have a table of users, say, with an ID and a name. I have a second table with two columns, both of which reference the users table by ID. What I need to do is somehow get the name of the user (or, say, top 5 users) who has/have the most combined references in either column of second table. I know this is more an SQL than a PHP question, but I thought it might be worth a shot to ask here.

[tave]

say your second table looks like this ....

id u_id
1 1
2 1
3 1
4 2
5 1
6 3
7 3
8 3
9 3
10 3
11 4
12 2
13 4

you can get the u_id and the number of posts like this

SELECT u_id, count( u_id ) AS Number_of_Posts
FROM DATA GROUP BY u_id
ORDER BY Number_of_Posts DESC 

which will give you this

u_id Number_of_Posts
3 5
1 4
2 2
4 2

then you can use a join or another query to get the names. You can also use limit on the query to get you the top 5 or 3 or whatever number you need

[Ryodox]

I knew about that, but my issue was with being able to do it on two columns and adding both COUNT(*) values together, grouped by user ID. I figured out the solution; I selected the user ID and name from the first table and left-joined two subqueries which were patterned after the solution posted.

[tave]

do you mind posting them so i can see?
i was not sure if you wanted to add col1 and col2 and display it as one column or if you wanted to sort first by col1 and then col2

i would really like to see how you did it