question

KwebenaAcquah-9104 avatar image
0 Votes"
KwebenaAcquah-9104 asked KwebenaAcquah-9104 edited

how to join up database table

i am trying to join 6 tables;
such as shown in this code below and show all in one datagridview
from sql database;



dotnet-csharpwindows-wpf
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

cheong00 avatar image
1 Vote"
cheong00 answered Viorel-1 commented

The join condition ("ON part") should be immediately following the corresponding "JOIN" part.

                  string sql = "SELECT s1.FirstName, s1.LastName, " +
                      "s2.EXAMSMATHEMATICS " +
                      "s3.TOTALMATHEMATICS" +
                      "s4.OVERALLTOTALMATHEMATICS" +
                      "s5.POSITIONMATHEMATICS," +
                      "s6.GRADEMATHEMATICS " +
                      "FROM(SELECT FirstName, LastName,ROW_NUMBER() OVER(ORDER BY FirstName)As rn1 FROM tbl_TestingTheApplicationsNAME)as s1 " +
                      "FULL OUTER JOIN(SELECT EXAMSMATHEMATICS,ROW_NUMBER() OVER (ORDER BY EXAMSMATHEMATICS)As rn2 FROM tbl_EXAMSSCORES)as s2 ON s1.rn1 = s2.rn2" +
                      "FULL OUTER JOIN(SELECT TOTALMATHEMATICS,ROW_NUMBER() OVER (ORDER BY TOTALMATHEMATICS)As rn3 FROM tbl_TOTALSCORES)as s3 ON s2.rn2 = s3.rn3" +
                      "FULL OUTER JOIN(SELECT OVERALLTOTALMATHEMATICS,ROW_NUMBER() OVER (ORDER BY OVERALLTOTALMATHEMATICS)As rn4 FROM tbl_OVERALLSCORES)as s4 ON s3.rn3 = s4.rn4" +
                      "FULL OUTER JOIN(SELECT POSITIONMATHEMATICS,ROW_NUMBER() OVER (ORDER BY POSITIONMATHEMATICS)As rn5 FROM tbl_POSITIONSCORES)as s5 ON s4.rn4 = s5.rn5" +
                      "FULL OUTER JOIN(SELECT GRADEMATHEMATICS,ROW_NUMBER() OVER (ORDER BY GRADEMATHEMATICS)As rn6 FROM tbl_GRADESCORE)as s6 ON s5.rn5 = s6.rn6";

Also note that your join condition doesn't make much sense because except rn1 which is over "FirstName", other ROW_NUMBER() are ordered by score fields.
Use some primary key fields for joining instead. There ought to be things like "Student Number" which should be unique key field for students.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

i am getting this error
INCORRECT SYNTAX NEAR THE KEYWORD "JOIN"
and also within all the "as" within my code i cant tell well please check it out again for me. i have tried checking but can't find any thing wrong with that code; it should work but its not; please help

0 Votes 0 ·
Viorel-1 avatar image Viorel-1 KwebenaAcquah-9104 ·

Make sure that the query includes enough ‘,’ and spaces.

0 Votes 0 ·