SQL Join returns too many rows

Question

This is a continuation of the SQL joining dilemma I had yesterday...

I have the following tables:

(Student)

|   ID    |  NAME   |
|  1234   |  dave   |

(Assessment)

|     ID     |    DATE    | STUDENT_ID |  TYPE  |
|     1      |  02/03/11  |    1234    | School |
|     2      |  05/03/11  |    1234    | Parent |

(Concern) 

|   ID   |  ASSESSMENT_ID  | TOPIC | LEVEL | TOPIC_NUMBER |
|    1   |         1       | apple |   3   |       1      |
|    2   |         1       | pears |   2   |       2      |
|    3   |         2       | mango |   2   |       1      |
|    4   |         2       | dates |   4   |       2      |

I want to create a single row which combines the assessments and concerns:

| StudentId | TIoC1 | TIoC2 | TIoCScore1 | TIoCScore2 | PIoC1 | PIoC2 | PIoCScore1 | PIoCScore2 |
|   1234    | apple | pears |      3     |      2     | mango | dates |      2     |       4    |

This is to be exported to a stats package, so the structure has to remain as is.

This is the SQL I have now:

SELECT a_assessment.StudentId, 
TIoC1,TIoC2,TIoCScore1,TIoCScore2,PIoC1,PIoC2,PIoCScore1,PIoCScore2
FROM
  dbo.Assessment a_assessment
  LEFT OUTER JOIN
  (
    SELECT P.StudentId, C1.Topic AS TIoC1, C2.Topic AS TIoC2, C3.Topic AS TIoC3, C4.Topic AS TIoC4,
      C1.Level AS TIoCScore1, C2.Level AS TIoCScore2, C3.Level AS TIoCScore3, C4.Level AS TIoCScore4
    FROM dbo.Assessment P
      LEFT JOIN Concern C1 ON P.Id = C1.Assessment_Id and C1.TopicNumber = 1
      LEFT JOIN Concern C2 ON P.Id = C2.Assessment_Id and C2.TopicNumber = 2
    WHERE P.Type = 'School'
  ) a_school_cons
  ON a_school_cons.StudentId = a_assessment.StudentId
  LEFT OUTER JOIN
  (
    SELECT P.StudentId, C1.Topic AS PIoC1, C2.Topic AS PIoC2
      C1.Level AS PIoCScore1, C2.Level AS PIoCScore2
    FROM dbo.Assessment P
      LEFT JOIN Concern C1 ON P.Id = C1.Assessment_Id and C1.TopicNumber = 1
      LEFT JOIN Concern C2 ON P.Id = C2.Assessment_Id and C2.TopicNumber = 2
    WHERE P.Type = 'Parent'
  ) a_parent_cons
ON a_parent_cons.StudentId = a_assessment.StudentId

The problem is, it returns two identical rows!

| StudentId | TIoC1 | TIoC2 | TIoCScore1 | TIoCScore2 | PIoC1 | PIoC2 | PIoCScore1 | PIoCScore2 |
|   1234    | apple | pears |      3     |      2     | mango | dates |      2     |       4    |
|   1234    | apple | pears |      3     |      2     | mango | dates |      2     |       4    |

And I can't work out how to update this to only return one - any help gratefully received!!

Thanks as always!

Answer 1

Instead of FROM Assessment LEFT JOIN... do FROM Student LEFT JOIN....

You only need to StudentID column, but the 2 rows in Assessment are retruning you two rows.

Answer 2

Doesn't adding a simple DISTINCT work here?