Asking Help on SQLITE 3 QUERY with Foreign Key

[sendai]

Hello i am asking for help regarding getting data from database with 2 foreign keys i am stuck in getting data in INNER JOIN
my query is i believe works fine but i need to print the 2 students who will be playing in the quiz.. every quiz there is 2 student who will join.

i have table QuizB with 2 foreign keys in Student Table

table QuizB
QuizB_ID PK
Student1_ID FK
Student2_ID FK

table Student
StudentID PK
StudentFirstName
StudentLastName

for row in db:nrows("SELECT * FROM QuizB Q INNER JOIN Student S1 ON Q.Studen1_ID = S1.StudentID INNER JOIN Student S2 ON Q.Student2_ID = S2.StudentID")

i = Grid.GetRowCount("Grid1");
Grid.InsertRow("Grid1", -1, false);
Grid.SetCellText("Grid1", i, 0, row.QuizB_ID, true);
Grid.SetCellText("Grid1", i, 1, "["..i.."] "..row.StudentFirstName.." "..row.StudentLastName.." || "..row.StudentFirstName.." "..row.StudentLastName, true);

end

i need to get the 2 student in the QuizB table
as of now the result is the same student name

example
[1.] WILL SMITH || WILL SMITH

instead of
[1.] JOHN DOE || WILL SMITH

thank you for any help.

[abood1987]

look at this help :

https://www.w3schools.com/sql/sql_join_inner.asp

or put your apz 4 test

[sendai]

Hi abood1987 i have seen some example in StockOverflow, looks the same in my query. But how can i print the value of u1.username and u2.username base in the example?

[sendai]

how can i print the u1.username and u2.username?

in sqlite if i get the username i used row.username but how about the u1.username?

i try row.u1.username but i got error..

i think that will be my problem now how i can get it...


Thanks

[sendai]

in this query it try

SELECT t1.StudentName, t2.StudentName
FROM [QuizB] g
INNER JOIN
Student t1 ON g.Student1_ID = t1.StudentID
INNER JOIN
Student t2 ON g.Student2_ID = t2.StudentID;


now in AMS how can i print the t1.StudentName and t2.StudentName?

row.t1.StudentName is error..

using row.StudentName will print the data in StudentName:1 which is the ELLEN.. i need both data in StudentName and StudentName:1 or
the Devorah and Ellen

Thank you.