mysql query help

Can any one help me for this query?

Table 1
-------------------
USER_ID | USERNAME
1 | my name
2 | name 2
3 | name 3


Table 2
-------------------
ID | USER_ID | RECORD
1 | 3 | that is data
2 | 3 | that is another data
3 | 3 | that is another data
4 | 1 ! that is another dat
5 | 1 | that is another data

I want to select * from table 1 where user_id = (select user_id from table2 where [count(id) is maximum, limit 10])

I am facing problem in this [count(id) is maximum, limit 10]
here i want to select top 10 ids which are counted maximum.

The query should return data from Table 1 as:
-------------------
USER_ID | USERNAME
3 | name 3 (because 3 is counted maximum at table 2)
1 | my name


Any one can help me???

Thanks
Burhan Khan

 

Can you phrase what you want to do in English? where user_id = will expect only one match. If you want more than one, you should use where user_id IN

 

As I understood you need to get Top 10 posters, right?
What version of MySQL do you use?

 

Yes exectly i want top 10 posters.
i am using MYSQL 4.1.21

 

[count(id) is maximum, limit 10]

Code (markup):

I'm not sure that is even proper syntax.

I would use 'ORDER BY' personally, try something like this:

SELECT * FROM table 1 WHERE user_id = (SELECT user_id FROM table2 ORDER BY id DESC LIMIT 0,10)

Code (markup):

Make sure that 'table 1' and 'table2' are the proper names of the tables or else this won't work.

Also, I think this will display the 10 latest posters, not the 10 top posters, but maybe that's what you wanted?

 

no, i want top posters.

 

Oh, well that's not really what the max() function is used for.

I think to display top posters you will have to do 2 querys; One to find the user_id which is the most common in the table, and a second to take that user ID and relate that to a username.

Let me think about this for a minute...

 

try this one, don't know if it'll work though

SELECT table1.username, table1.record, COUNT(table2.user_id) as total
FROM table1 
LEFT JOIN table2 ON (table1.user_id = table2.user_id)
GROUP BY table2.user_id
ORDER BY total DESC
LIMIT 10

Code (markup):

 

It is not working, can any one help me??

 

There's nothing wrong with discoverclips's query.