Announcement

Collapse
No announcement yet.

Help on SQLITE3 JOIN

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Help on SQLITE3 JOIN

    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



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

    Comment


    • #3
      any help would be appreciated.. thank you..

      Comment


      • #4
        i have found this example in Stock

        Click image for larger version

Name:	Screenshot_126.png
Views:	103
Size:	13.1 KB
ID:	306115

        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..

        Comment


        • #5
          Click image for larger version

Name:	SCRN-2021-02-12-01.png
Views:	102
Size:	34.0 KB
ID:	306117

          Comment


          • #6
            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..

            Comment


            • #7
              Click image for larger version

Name:	Screenshot-128.png
Views:	130
Size:	7.6 KB
ID:	306120

              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.

              Comment


              • #8
                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:

                Click image for larger version

Name:	SCRN-2021-02-12-02.png
Views:	284
Size:	4.3 KB
ID:	306122
                Here is the "users" table:

                Click image for larger version

Name:	SCRN-2021-02-12-03.png
Views:	86
Size:	5.3 KB
ID:	306123
                Here is the code, based on my previous SQL:

                Code:
                -- 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:

                Click image for larger version

Name:	SCRN-2021-02-12-04.png
Views:	93
Size:	8.5 KB
ID:	306124

                Ulrich

                Comment


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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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..

                      Comment

                      Working...
                      X