Database with two tables

[lemodizon]

Hello everyone,

I created a databases (sqlite) with two tables (this is my first time to do this I used in one table). I already linked them but i'm having a problem in my combo box and in my section field. I want in my combobox to display the list of the section name(not secID) here is my code below. Thanks in advance everyone

Code: Select all

command Sections
   local tlist
   put AllMyLearnersList() into tlist
   set the text of btn "SectionList" to tlist
end Sections


function AllMyLearnersList
   global gDatabaseID, 
   local tSQLStatement, tlist
   
   // i tried to changed it into SecName it will display the list of sectionName but there will no link in two tables.
   put "SELECT SecID FROM Sections "into tSQLStatement
   put revDataFromQuery(tab,return,gDatabaseID,tSQLStatement) into tlist
   return tlist 
   
end AllMyLearnersList

This code below will get the section ID based on the combobox selected by the user.

Code: Select all

command GetStudentInfo
   local tLRN, tStudentName, tSectionName,tSectionID_FK
   local tStudentID
   
   put fld "LRNFld" into tLRN
   put fld "StudentNameFld" into tStudentName
   put the label of btn "SectionList" into tSectionName
   // this is the part where i will get the sectionID from the combobox
   put the label of btn "SectionList"  into tSectionID_FK
   put fld "StudentIDFld" into tStudentID
   
   
   if tStudentID is empty then
      SaveSaveInfo tSectionID_FK,tLRN,tStudentName,tSectionName
   end if
end GetStudentInfo

[bobcole]

Here's my guess as to the SQLite query that will provide the "secName" field:

Code: Select all

SELECT stu.studentID
		,stu.studentName
		,stu.LRN
		,stu.sectionID
		,sec.secName
FROM main.students stu 
LEFT OUTER JOIN main.sections sec 
	ON sec.secID = stu.sectionID;

The join connects the two tables where the section ids match.
The first four fields are selected from the students (stu) table. The fifth field is the section name which selected from the sections (sec) table.
Just a guess from memory. I haven't actually run this in SQLite but I think it is worth a try.
Bob

[lemodizon]

Hi bobcole,

Thanks I will try this one.