CODEWITHSUNDEEP
mysqlsql
Pharm
Pharm

Reputation: 162

Advanced MySQL Query (ranking results based on 2 other tables)

I've been trying to make this work properly for a while now but just can't get my head around how to go about it, hopefully someone here can help me!

I have three tables:

Table A
Table B
Table C

I want to get the top 10 results from Table A based on a ranking that will depend on information from table B and table C. The ranking will be using the following formula:

Ranking = (COUNT(id) 
    from table C 
    WHERE c.a_id = a.id) as count_weight + 
    (COUNT(id) 
    FROM table B 
    WHERE b.a_id = a.id)*(count_weight*0.25) + a.views

In words I want the ranking to be equal to a point value determined by:

  1. The count of records in Table C that correspond to the record in Table A that I'm interested in
  2. If a record exists in Table B that corresponds to the record in Table A I'm interested in, I want an additional increase in the points by 25% (taking the points gained from #1 and multiply by 0.25) - In this case a record will exist or wont exist so it will always be 0 or 1
  3. Points for each "view" for the record in Table A (a field of table A)

Hopefully I worded that in a understandable manner!

Thanks!

Upvotes: 1

Views: 125

Answers (1)

Niall
Niall

Reputation: 456

I think this is what you're after:

SELECT a.*, COUNT(c.id) * IF(COUNT(b.id),1.25,1) + a.views AS Ranking
FROM a LEFT JOIN c ON a.id = c.a_id LEFT JOIN b ON a.id = b.a_id
GROUP BY a.id
ORDER BY Ranking DESC
LIMIT 10

If you don't want to select the Ranking, you can put that column's formula directly into the ORDER BY clause.

Upvotes: 2

Related Questions