How to best approach this subquery?

[sw0o0sh]

Hi, I am working on a query for a highscores feature, that shows the amount of "kills" a certain player has made.

 

Here is an example of the main table format:

rsca2_players, which has the columns .user, .pass, .join_time, etc

 

It stores their kills in a table called

rsca2_kills, which has a common column .user, the person they killed: .killed, the .time it happened, etc.

 

So naturally, if a user gets a lot of kills, they have a lot of rows in the rsca2_kills table.

 

What I am trying to do is make it so I get a query that returns who has the most kills, (their .user id), and the COUNT of the kill rows. Is there any way to do this? I made a query but it never finishes loading, I'm assuming it is not efficient? Is there something else that can be done to make this work?

[kickstart]

Hi

 

Probably best to do it with a JOIN

 

Something like this

 

SELECT a.user, COUNT(b.killed)

FROM rsca2_players a

LEFT OUTER JOIN rsca2_kills b

ON a.user = b.user

GROUP BY a.user

 

All the best

 

Keith