CODEWITHSUNDEEP
mysql
buzibuzi
buzibuzi

Reputation: 734

using RIGHT OUTER JOIN with COUNT(*)

Im having an issue with a join query. I have 3 tables:

machines

+-----+--------+
| mid | points |
+-----+--------+
|  12 |     25 |
|  22 |     50 |
|  23 |     75 |
|  30 |     90 |
+-----+--------+

challenges

+----+-----+-----+
| id | mid | uid |
+----+-----+-----+
|  1 |  22 |   9 |
|  2 |  23 |  10 |
|  3 |  29 |  13 |
|  4 |  12 |  10 |
+----+-----+-----+

user_hints

+----+-----+-----+
| id | mid | uid |
+----+-----+-----+
|  1 |  22 |  10 |
|  2 |  23 |  10 |
|  3 |  30 |  10 |
|  4 |  22 |  11 |
+----+-----+-----+

Im trying to create a single query that will collect into a single row some stats for a specific user.

  1. for all records in CHALLENGES table that have the corresponding user id ( "uid" column) - calculate sum total of points from MACHINES table "points" column.they are related by "mid" column. if no records/points, return 0.
  2. for the same user id (uid column) - count the number of records in the USER_HINTS table and join it to results from first query.

the expected result for this is be:

+--------+------------+
| points | totalhints |
+--------+------------+
|    100 |          3 |
+--------+------------+

this is my query:

SELECT challenges.id, 
        IFNULL(
        ( SELECT SUM(m.points) as points
        FROM challenges c
        INNER JOIN machines m ON (c.mid = m.mid AND c.uid = 10)
                GROUP BY c.uid),'0') AS points,
        COUNT(*) as totalhints
        FROM challenges 
        RIGHT OUTER JOIN user_hints uh ON user_hints.uid = 10
        GROUP BY points

but the totalhints is off and is counting all rows. can i get some help with the last INNER JOIN part ? thanks

Upvotes: 0

Views: 113

Answers (1)

Strawberry
Strawberry

Reputation: 33945

You can do something like the following, but really, when you're mashing together different kinds of things, I think it's best to do that in separate queries...

SELECT DISTINCT j.uid
     , COALESCE(k.points,0) points
     , COALESCE(l.hints,0) hints
  FROM challenges j
  LEFT
  JOIN (SELECT c.uid, SUM(points) points FROM challenges c JOIN machines m ON m.mid = c.mid GROUP BY c.uid) k
    ON k.uid = j.uid
  LEFT
  JOIN (SELECT uid, COUNT(h.id) hints FROM user_hints h GROUP BY uid) l
    ON l.uid = j.uid;

Upvotes: 1

Related Questions