Multiplying COUNT from 2 tables?

[RJP1]

Hi guys,

 

I'm looking to get a little help on a complex issue...

 

I have 2 tables. Both have user ids.

 

I'd like to make a leader board from the data within each table...

 

I need to count each row in one table and get that as a simple number per user. Then for each user id, get the count from another table and then multiply these 2 figures together.

 

Then order by the "score" DESC... Can anyone help me find the best way to do this?

 

Thanks!

 

RJP1

[RJP1]

Essentially I need to combine these 2 queries:

 

SELECT p.userid, COUNT(p.status) AS score FROM table1 AS p
WHERE (SELECT COUNT(*) FROM usertable WHERE id = p.userid)>0
AND p.date_joined >= 0 AND p.date_joined <= ".time();
GROUP BY p.userid
ORDER BY score DESC
LIMIT 10";

 

And

 

SELECT userid, (1 + (COUNT(*) * 0.1)) AS score2 FROM table2 WHERE time >= 0 AND time <= FROM_UNIXTIME($now) GROUP BY userid ORDER BY score2 DESC

 

Basically, I'd like to get score and score2 and multiply them together in 1 query... Doable?

 

Cheers,

 

RJP1

[fenway]

By the looks of it, the query conditions are totally different.

[RJP1]

Well, they dont have to be different, I just need a little help to multiply 2 counts from 2 different tables really.

[fenway]

But you have 3 tables.

[RJP1]

Ignore the user table, it's simply a check to see if the user exists...

[fenway]

How are score and score2 different?