Mysql Cross Joining

Question

I have three Tables

Countries
id | name
1  | USA
2  | UAE
3  | UK
4  | INDIA

Users 

id | name  | countryid
1  | Philip| 1
2  | Aby   | 3
3  | Sam   | 3

Happiness
id | userid | mark
1  | 1      | 10
1  | 2      | 50
1  | 3      | 70

I need to get a result of happiness country ranking as

Rank | Country | Total
1    | UK      | 120
2    | UAE     | 10
3    | USA     | 0
4    | INDIA   | 0

I need a mysql query for this solution..

You need an LEFT OUTER JOIN of Countries to Users and then an LEFT OUTER JOIN of Users to Happiness, with a GROUP BY Country and a SUM of mark. While it is possible to get the rank as well in SQL, it is not elegant and that is probably easier to do in the script that processes the results. — Kickstart, May 27 '14 at 12:38

score 2 · Accepted Answer · edited May 23 '17 at 12:08

Maybe something like this:

SET @rank=0;
SELECT
  @rank:=@rank+1 AS rank,
  tbl.*
FROM
(
    SELECT
      Countries.name,
      COALESCE(SUM(Happiness.mark),0) AS Mark
    FROM
      Countries
      LEFT JOIN Users
        on Countries.id=Users.countryid
      LEFT JOIN Happiness
        ON Happiness.userid=Users.id
    GROUP BY
      Countries.Name
) AS tbl
ORDER BY tbl.Mark DESC

References:

SQL fiddle here

Mysql Cross Joining

1 Answers1