Help on SQLITE3 JOIN

[telco]

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")


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.


Telco

[telco]

i forget to include this script i = Grid.GetRowCount("Grid1"); before insertRow

[telco]

any help would be appreciated.. thank you..

[telco]

i have found this example in Stock



i want to achieve the same query but i am stuck on how to get the two username or StudentFirstName in my example..

i have tried row.StudentFirstName[1] but does not work..

[Ulrich]

[telco]

Hi ulrich.. your screenshot is works fine as usual... my problem is how can i pass the value of u1 and u2 to AMS object?

example:

Label.SetText("lbl1", u1.fname) --- this is not working
Label.SetText("lbl2", u2.fname) --- this is not working

Label.SetText("lbl1", row.u1.fname) --- this is not working
Label.SetText("lbl2", row.u2.fname) --- this is not working

if i use

Label.SetText('lbl1", row.fname) works fine but i was able to get 1 of them only..

[telco]

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.

[Ulrich]

There is no "INNER JOIN" in the SQL I presented, so you are back to doing something else...

This is my "myform" table for testing:


Here is the "users" table:


Here is the code, based on my previous SQL:

-- open database
SQLite3Connection, err = SQLite3:connect("AutoPlay\\Docs\\sample.db");
if not SQLite3Connection and err then
    Dialog.Message("Error", err);
else
   -- retrieve data
   SQLite3Cursor = SQLite3Connection:execute([[SELECT
      u1.fname as fn1,
      u1.lname as ln1,
      u2.fname as fn2,
      u2.lname as ln2
   FROM
      user as u1,
      user as u2,
      myform
   WHERE
      myform.user1 = u1.id AND
      myform.user2 = u2.id
   ORDER BY
      myform.id]]);

   -- prepare Grid
   i = 1;
   row = SQLite3Cursor:fetch({},"a");
   Grid.DeleteAllItems("Grid1");
   Grid.SetColumnCount("Grid1", 5);
   Grid.InsertRow("Grid1", -1, false);
   Grid.SetCellText("Grid1", 0, 0, "#");
   Grid.SetCellText("Grid1", 0, 1, "First Name (1)");
   Grid.SetCellText("Grid1", 0, 2, "Last Name (1)");
   Grid.SetCellText("Grid1", 0, 3, "First Name (2)");
   Grid.SetCellText("Grid1", 0, 4, "Last Name (2)");

   -- print data
   while row do
      Grid.InsertRow("Grid1", -1, false);
      Grid.SetCellText("Grid1", i, 0, i);
      Grid.SetCellText("Grid1", i, 1, row.fn1);
      Grid.SetCellText("Grid1", i, 2, row.ln1);
      Grid.SetCellText("Grid1", i, 3, row.fn2);
      Grid.SetCellText("Grid1", i, 4, row.ln2);
      i = i + 1;
      row = SQLite3Cursor:fetch(row,"a");
   end

   -- adjust columns
   Grid.AutoSizeColumn("Grid1", 0, GVS_BOTH, false, false);
   Grid.AutoSizeColumn("Grid1", 1, GVS_BOTH, false, false);
   Grid.AutoSizeColumn("Grid1", 2, GVS_BOTH, false, false);
   Grid.AutoSizeColumn("Grid1", 3, GVS_BOTH, false, false);
   Grid.AutoSizeColumn("Grid1", 4, GVS_BOTH, false, false);
   Grid.Refresh("Grid1");

   -- close everything
   SQLite3Cursor:close();
   SQLite3Connection:close();
end

Result:



Ulrich

[telco]

thanks Ulrich, i will check on this after breakfast.. im using Sqlite3 plugin anyway not the builtin sqlite3 in AMS..

[telco]

actually the with INNER JOIN and without i can query the data in 2 table the only problem i have is how to pass the data into grid for example..

i used this example i see this here in the forum..
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

[telco]

Hi Ulrich thank you so much for your help, i was able to eliminate my INNER JOIN query and use your example.. now it works fine..

Again thank you so much.. this is my first time using sqlite3 and offcors just self though learn with the help in this forum...

Regards

Telco..