How do I join the results of two queries in SQL Server?

Question

This is my second SQL question today - I'm a bit of a newbie on the DBA stuff...

I am trying to join together a complex sql query to merge the data in about 12 tables into 1 single table. Although there are one to many relationships in the database, I know what the maximum numbers of each are to be.

So, I have (with stack overflow's help!) flatten out the first level of my database, and have a pair of queries, which must now be joined together:

(abridged)

SELECT 
  A.StudentId, C1.Topic AS SIoC1, C1.Level AS SIoCScore1
FROM Assessment A
  LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type = 'School'

SELECT 
  A.StudentId, C1.Topic AS PIoC1, C1.Level AS PIoCScore1
FROM Assessment A
  LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type = 'Parent'

Is it possible to name queries as aliases?
Or how else can I join these two queries so the output is like:

| A.Id | SIoC1 | SIoCScore1 | PIoC1 | PIoCScore1 |

** UPDATE ** The domain behind it is that an Assessment is carried out, on which both the school and the parent must report. So the single row identifies an assessment which has both School and Parent values on it.

I'm using SQL Server 2005.

Thanks!

* FURTHER UPDATE * This query seems to do the job...

SELECT PreConcerns.StudentId, TIoC1, TIoCScore1, PIoC1, PIoCScore1 
FROM
  Assessment PreConcerns
  LEFT OUTER JOIN
  (
    SELECT 
      P.StudentId, C1.Topic AS TIoC1, C1.Level AS TIoCScore1
    FROM Assessment  P
      LEFT JOIN Concern C1 ON P.Id = C1.Assessment_Id and C1.TopicNumber = 1
    WHERE P.Type = 'School'
  ) scons
  ON scons.StudentId= PreConcerns.StudentId
  LEFT OUTER JOIN
  (
    SELECT 
      P.StudentId, C1.Topic AS PIoC1, C1.Level AS PIoCScore1
    FROM Assessment  P
      LEFT JOIN Concern C1 ON P.Id = C1.Assessment_Id and C1.TopicNumber = 1
    WHERE P.Type = 'Parent'
  ) pcons
ON pcons.StudentId = PreConcerns.StudentId

FURTHER UPDATE (take 2!) ** (I'm not sure if I should reopen this as a new question??!) I got back to work today, to find my above 'solution' didn't quite solve the problem - it creates two rows for each assessment.

So to recap, I have the following tables:

Student (int:id, varchar(50):name)
Assessment (int:id, date:date, int:StudentId, )
Concern (int:id, int:assessment_id, varchar(20):topic, int:level)

So for each student in the system there is exactly two Assessments - one with type 'School', and one with type 'Parent'.

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

(pseudo columns:)

| Assessment.StudentId | SchoolConcernTopic | SchoolConcernLevel | ParentConcernTopic | ParentConcernLevel |

or from the sql above:

| PreConcerns.StudentId | SIoC1 | SIoCScore1 | PIoC1 | PIoCScore1 |

With only one row populated per student, which combines both assessments. I have the structure for this working with the above SQL, but it returns two rows! And I can't work out how to update this to only return one - any help gratefully received!!

Thanks as always!

Answer 1

This can be done with just one query. The IN clause is just a fancy way of saying A.Type = 'School' OR A.Type = 'Parent'

SELECT 
  A.Id, C1.Topic AS PIoC1, C1.Level AS PIoCScore1
FROM Assessment A
  LEFT JOIN Concern C1 ON A.Id = Assessment_Id and C1.TopicNumber = 1
WHERE A.Type IN ('School','Parent')