Counting results for cross join

When you join to tables together you need to think of it comparing each row from the first table to each row of the second table and then returning the ones that match based on the join condition. The join condition is specified in the ON clause.

In almost all cases you want to reference both tables in the ON clause. Let's look at your example in SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Question (
  QID INT,
  QTxt VARCHAR(150),
  QOrder INT);

CREATE TABLE User (
  UID INT,
  UserName VARCHAR(150));

CREATE TABLE ANSWER (
  AID INT,
  ATxt VARCHAR(150),  
  QID INT,
  USERID INT);




INSERT INTO User VALUES
 (1, 'Alex'),
 (2, 'Sarah'),
 (3, 'Tom');

INSERT INTO Question VALUES
 (1, 'Age', 1),
 (2, 'Gender', 2),
 (3, 'Weight',3),
 (4, 'Height',4);

INSERT INTO Answer VALUES
 (1, '30', 1 , 1),
 (2, '27', 1,2),
 (3, '30', 1,3),
 (4, 'M', 2,1),
 (5, 'F', 2,2),
 (6, 'M', 2,3),
 (7, '61', 4,1),
 (8, '165', 3,1);

The ON condition in your case is Answer.QID = Question.QID:

Query 1:

SELECT *
  FROM Answer
  JOIN Question
    ON Answer.QID = Question.QID
 WHERE Answer.UserId = 3

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER |
|-----|------|-----|--------|--------|--------|
|   3 |   30 |   1 |      3 |    Age |      1 |
|   6 |    M |   2 |      3 | Gender |      2 |

You can use aliases to shorten that query a little:

Query 2:

SELECT *
  FROM Answer AS A
  JOIN Question AS Q
    ON A.QID = Q.QID
 WHERE A.UserId = 3

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER |
|-----|------|-----|--------|--------|--------|
|   3 |   30 |   1 |      3 |    Age |      1 |
|   6 |    M |   2 |      3 | Gender |      2 |

The same way you can now also join in the User table:

Query 3:

SELECT *
  FROM Answer AS A
  JOIN Question AS Q
    ON A.QID = Q.QID
  JOIN User AS U
    ON A.UserId = U.UID
 WHERE U.UserName = 'Tom'

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER | UID | USERNAME |
|-----|------|-----|--------|--------|--------|-----|----------|
|   3 |   30 |   1 |      3 |    Age |      1 |   3 |      Tom |
|   6 |    M |   2 |      3 | Gender |      2 |   3 |      Tom |

For more information on JOINs check out my JOIN series here: A Join a Day

It is written about Microsoft SQL Server, but most of it works in other database management system too.

JOINs together with aggregates (eg, SUM()) often give you bigger values than you expect. To see this, remove the SUMs and GROUP BY and stare at the output. You may see, for example, the same mghours.hourstime showing up multiple times. Hence, SUM(mghours.hourstime) is inflated.

This is happening because JOIN mgtraining is coming up with multiple rows in mgtraining for each row in mghours.

The solution is to do the query in steps. Notice how the inner query avoids inflating "vol":

SELECT  x.*,
        SUM(...) AS 'at
    FROM (
        SELECT  ...,
                SUM(mghours.hourstime) AS vol
            FROM ...
            GROUP BY ...
         ) x
    LEFT JOIN mgtraining ON ...
    GROUP BY ...
    ORDER BY ...;