Combine 3 Selects 1

[saintedmunds]

HI There

HOw do I combine these 3 select statements? I can do two but having trouble with 3 seems to be the last bit of code.

ALTER PROCEDURE dbo.spSymbolsByForm
(@TermID int)

AS

Select A.*,
B.*
From (

SELECT studentID, EnglishA AS EnglishA1, EnglishE AS EnglishE1, EnglishComment AS EnglishComment1, MathsA AS MathsA1,
MathsE AS MathsE1, MathsComment AS MathsComment1, ScienceA AS ScienceA1, ScienceE AS ScienceE1, ScienceComment AS ScienceComment1,
FrenchA AS FrenchA1, FrenchE AS FrenchE1, FrenchComment AS FrenchComment1, HistoryA AS HistoryA1, HistoryE AS HistoryE1,
HistoryComment AS HistoryComment1, GeographyA AS GeographyA1, GeographyE AS GeographyE1, GeographyComment AS GeographyComment1,
LatinA AS LatinA1, LatinE AS LatinE1, LatinComment AS LatinComment1, RSEffort AS RSEffort1, ArtEffort AS ArtEffort1, MusicEffort AS MusicEffort1,
ICTEffort AS ICTEffort1, PEEffort AS PEEffort1, OtherComments AS OtherComments1, TutorTargets AS TutorTargets1, SpecialNeeds AS SpecialNeeds1,
TotalEffort AS TotalEffort, StaffOnlyComments AS StaffOnlyComments1
FROM dbo.tSymbols
WHERE (SymbolsID = 1) AND (TermID = @TermID)

) As A
Inner Join
(

SELECT studentID, EnglishA AS EnglishA2, EnglishE AS EnglishE2, EnglishComment AS EnglishComment2, MathsA AS MathsA2,
MathsE AS MathsE2, MathsComment AS MathsComment2, ScienceA AS ScienceA2, ScienceE AS ScienceE2, ScienceComment AS ScienceComment2,
FrenchA AS FrenchA2, FrenchE AS FrenchE2, FrenchComment AS FrenchComment2, HistoryA AS HistoryA2, HistoryE AS HistoryE2,
HistoryComment AS HistoryComment2, GeographyA AS GeographyA2, GeographyE AS GeographyE2, GeographyComment AS GeographyComment2,
LatinA AS LatinA2, LatinE AS LatinE2, LatinComment AS LatinComment2, RSEffort AS RSEffort2, ArtEffort AS ArtEffort2, MusicEffort AS MusicEffort2,
ICTEffort AS ICTEffort2, PEEffort AS PEEffort2, OtherComments AS OtherComments2, TutorTargets AS TutorTargets2, SpecialNeeds AS SpecialNeeds2,
TotalEffort AS TotalEffort, StaffOnlyComments AS StaffOnlyComments2
FROM dbo.tSymbols
WHERE (SymbolsID = 2) AND (TermID = @TermID)
) As B

Inner Join
(




SELECT EnglishA AS EnglishA3, EnglishE AS EnglishE3, EnglishComment AS EnglishComment3, MathsA AS MathsA3,
MathsE AS MathsE3, MathsComment AS MathsComment3, ScienceA AS ScienceA3, ScienceE AS ScienceE3, ScienceComment AS ScienceComment3,
FrenchA AS FrenchA3, FrenchE AS FrenchE3, FrenchComment AS FrenchComment3, HistoryA AS HistoryA3, HistoryE AS HistoryE3,
HistoryComment AS HistoryComment3, GeographyA AS GeographyA3, GeographyE AS GeographyE3, GeographyComment AS GeographyComment3,
LatinA AS LatinA3, LatinE AS LatinE3, LatinComment AS LatinComment3, RSEffort AS RSEffort3, ArtEffort AS ArtEffort3, MusicEffort AS MusicEffort3,
ICTEffort AS ICTEffort3, PEEffort AS PEEffort3, OtherComments AS OtherComments3, TutorTargets AS TutorTargets3, SpecialNeeds AS SpecialNeeds3,
TotalEffort AS TotalEffort, StaffOnlyComments AS StaffOnlyComments3
FROM dbo.tSymbols
WHERE (SymbolsID = 3) AND (TermID = @TermID)
) As C

on a.studentID = b.studentid on b.studentID = c.StudentID


Any Help would be great

Thank you

 

[skuhlman]

Your first JOIN is missing the ON clause

select *
FROM ( ) as A
JOIN ( ) as B ON A.ID = B.ID
JOIN ( ) as C on A.ID = C.ID

 

[GeertVerhoeven]

Hi,

If the columns are the same for all the queries, you can better use the UNION command.

See:

http://www.w3schools.com/sql/sql_union.asp

Greetz,

Geert


Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[skuhlman]

The choice of Unions vs. Joins depends upon how you want the results to look. If you want A, B, and C data to all appear on a single record, you should use the JOIN. If you want A, B, and C to appear as 3 records with the same ID, you should use a UNION.

 

[saintedmunds]

Hi

THank you Skuhlman Join is the way i need to go.
The problem I am now having is that it only shows records where there is data in all 3 selects. I need to show all data even if one of the selects is empty.

I have tried left Join etc but doesnt seem to help.

Any ideas?

Cheers

 

[GeertVerhoeven]

You will need to use a FULL OUTER JOIN since LEFT will still filter the results.

Greetz,

Geert



Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog