SQL Inner Join et al

[deanathpc]

Can someone explain how to do the inner, outer, right and left join for SQL in PB? I'm thinking that I'm just not getting it. A good example would be great too. I think I need a different point of view on it maybe.

You see in my biggest project that I am working on I have multiple tables. I'm trying to reduce the amount of data in some tables for obvious reasons. The problem I'm having is trying to pull data from multiple tables to fill in the gaps per se.

Here is my setup (roughly). I have a table for course records (CPR/ First Aid teaching biz). From there I have a few tables to collect some data like the students in that class / course. For each student I am able to track the results of pass / fail etc. I have a student table though that has all of their demographics (names, address, phone numbers etc. for mailing and printing cards). On the course record screen I have a quick listicon for the students who attended that particular course. I want to store record numbers only along with the results and pull the name from the main student table. I know this is possible as I have done this before in FileMaker Pro. But now that I have to actually do the language and not just point and click I am stuck. For now I have added the first and last name of the student in the table. Just a pain the more data you try to collect when using SQL should simplify it maybe?

Does this make sense? Let me know if you need clarification on this. This project has come a long way since I started and with this little issue it could be finished and streamlined so much better. Or am I doing some wishful thinking?

Thanks in advance!

Dean

[swan]

Hi,
Perhaps a list of tables/columns will help us understand.
I use inner joins a bit but forward planning is always a blessing.
Also, what database are you planning on using ?
Cheers,

[spikey]

Its going to be something like:-

Code: Select all

Query.s = "SELECT records.record_id, records.result, records.student_id, students.name " +
          "FROM records " +
          "LEFT JOIN students ON records.student_id = student.student_id " +
          "WHERE XXXX;"

but Swan is correct we'd be able to give you a much better solution if we knew what the table and column names actually are!

[deanathpc]

Ok tonight I can do table names and the like. Or tomorrow morning. I just wasn't sure if inner join was a popular thing or not. Or if maybe a specialized type of thing. I'll get the table info in here and explain better. I have a feeling I didn't plan it out well.

Dean

[deanathpc]

Hi,
Perhaps a list of tables/columns will help us understand.
I use inner joins a bit but forward planning is always a blessing.
Also, what database are you planning on using ?
Cheers,

I'm using PostgreSQL but am considering sqlite for portability.

[bobobo]

Here is your Playground

Code: Select all

UseSQLiteDatabase()
;Debug DeleteFile("D:\squ.sqlite",#PB_FileSystem_Force)
;Debug CreateFile(0,"D:\squ.sqlite")
;CloseFile(0)
;If OpenDatabase(0,"D:\squ.sqlite","","",#PB_Database_SQLite)
If OpenDatabase(0,":memory:","","",#PB_Database_SQLite)
  ;Memory journaling to speed it up, not needed with "memorysqlites"
  ; DatabaseUpdate(0,"PRAGMA journal_mode =MEMORY")
  query.s="Create table records (record_id,result,student_id)"
  DatabaseUpdate(0,query)
  
  If DatabaseError()<>try.s
    Debug DatabaseError()+query
    try=DatabaseError()
  EndIf
  
  query.s="Create table students (student_id,name)"
  DatabaseUpdate(0,query)
  If DatabaseError()<>try
    Debug DatabaseError()+query
    try=DatabaseError()
  EndIf
  For i=1 To 300
    query.s="Insert into records values("+Str(i)+",'result"+Str(i)+"',"+Str(i+200)+")"
    DatabaseUpdate(0,query)
    If DatabaseError()<>try
      Debug DatabaseError()+query
      try=DatabaseError()
    EndIf
  Next i
  For i= 200 To 300
    query.s="Insert into students values("+Str(i)+",'Name "+Str(i)+"')"
    DatabaseUpdate(0,query)
    If DatabaseError()<>try
      Debug DatabaseError()+query
      try=DatabaseError()
    EndIf
  Next i
Else
  If DatabaseError()<>try
    Debug DatabaseError()+query
    try=DatabaseError()
  EndIf
EndIf
;Ok we have data
;now some queries
;Left join

;while assembling a string as follows, be aware not to forget the space in front of the string parts
Query.s = "SELECT records.record_id, records.result, records.student_id, students.name" +
          " FROM records" +
          " LEFT JOIN students ON records.student_id = students.student_id"+
          " Where students.name like 'Name 3%'"
Debug "Left joins"
If DatabaseQuery(0,query)
  While NextDatabaseRow(0)
    Debug "RecID:"+GetDatabaseString(0,0)
    Debug "RecREsult:"+GetDatabaseString(0,1)
    Debug ",StudId:"+GetDatabaseString(0,2)
    Debug ",StudName+"+GetDatabaseString(0,3)
    Debug "*************"
  Wend
  FinishDatabaseQuery(0)
Else
  If DatabaseError()<>try
    Debug DatabaseError()+query
    try=DatabaseError()
  EndIf
EndIf

CloseDatabase(0)

[deanathpc]

Here is the break down for a couple of my tables.

Courses (Course Records):

id serial PRIMARY KEY
courseid TEXT
initialrenewal TEXT
classtype TEXT
heartsaveroptions TEXT
optionssummary TEXT
instructionorganization TEXT
filelink TEXT
coursestatus TEXT
sentto TEXT
startdate TEXT
enddate TEXT
issuedate TEXT
expiredate TEXT
starttime TEXT
endtime TEXT
classhours TEXT
initialtime TEXT
renewtime TEXT
studentmanikinratio TEXT
customerid TEXT
customername TEXT
locationid TEXT
locationname TEXT
customeraddress1 TEXT
customeraddress2 TEXT
customercity TEXT
customerstate TEXT
customerzip TEXT
customerzip4 TEXT
customerphone TEXT
customerphoneext TEXT
customercontact TEXT
leadinstid TEXT
leadinstname TEXT
leadinstexpires TEXT
financestandardrate TEXT
financestudentfee TEXT
financetotalincoming TEXT
financebookcosts TEXT
financematerialscosts TEXT
financecardscosts TEXT
financialoverhead TEXT
financialroomrental TEXT
financialleadinstructor TEXT
financialassistantinstructor TEXT
financialinstructortotal TEXT
financialestimateperstudentprofit TEXT
financialestimateclassprofit TEXT
financialactualperstudentprofit TEXT
financialactualclassprofit TEXT
coursenotes TEXT
coursetypedescription TEXT
datecreated TEXT
datemodified TEXT
attendanceestimate TEXT
attendanceactual TEXT
statscomplete TEXT
statsincomplete TEXT
statsrenewals TEXT
statsinitials TEXT
statsremediated TEXT
-------------------------------
Students (Student Records)

id serial PRIMARY KEY
StudentID TEXT
LastName TEXT
NameExt TEXT
FirstName TEXT
MI TEXT
Address1 TEXT
Address2 TEXT
City TEXT
State TEXT
ZipCode TEXT
Zip4 TEXT
County TEXT
Country TEXT
Phone1 TEXT
Phone1Type TEXT
Phone2 TEXT
Phone2Type TEXT
EMail TEXT
Organization TEXT
Occupation TEXT
Notes TEXT
cashonly TEXT
datecreated TEXT
datemodified TEXT

StudentCourseHistory (Record for each student)

id serial PRIMARY KEY
StudentID TEXT
CourseID TEXT
BLSCardValid TEXT
MaterialsSent TEXT
WrittenTest TEXT
Overall TEXT
CardIssueDate TEXT
CardExpiresDate TEXT
RemediationDate TEXT
DateEntered TEXT
EnteredBy TEXT
ELearning TEXT
SkillCheckDate TEXT
NotesComments TEXT
DateCreated TEXT
DateModified TEXT
LastName TEXT
FirstName TEXT

There are three tables. There are a few more but this is the key to my question. As you can see by the history I added the first name and last name while saving data. But I wanted to eliminate that just to make the data saving routine easier. And why keep redundant data?

So in the Course screen I have a ListIcon Gadget that lists the students for that course. That entire record is pulled from another ListIcon Gadget at the bottom with the entire dataset for Courses. You click on a line with the course you want and it pulls that Course Record and populates the screen. Then I want the Students populated also based on the CourseID and then the StudentID needs to be reference to pull the Last Name and First Name for that student.

I hope this makes sense. Should I include a screen capture of what I'm talking about too? Would that make it easier? What is a good and cheap (read free) screen capture program to use?

Dean

[blueb]

What is a good and cheap (read free) screen capture program to use?
Dean

Dean... I've used CaptureScreen.exe for a couple of years. Dead easy to use and no installation routines (everything in one folder)

The latest version is 2012, but I have no problems. And it has a very good help file for various effects.

Here's the CNet download site.

NOTE - When installing..Decline to install all the "extra" programs they offer.

http://download.cnet.com/CaptureScreen/ ... 60661.html

[deanathpc]

Here is a screen shot:

[swan]

spikey's & bobobo's suggestions above are very close I think. Just change the table names & columns to suit.

Code: Select all

Query.s = "SELECT c.classtype, s.FirstName, s.LastName" +
          " FROM courses As c" +
          " LEFT JOIN students As s ON r.customerid = s.id"+
          " Where s.name like 'Name 3%'"

I think your table designs need a little work. I don't think all text is a great idea. For instance id's should be at least integers and dates should be date/time stamps otherwise searching between dates will be difficult.

[deanathpc]

spikey's & bobobo's suggestions above are very close I think. Just change the table names & columns to suit.

Code: Select all

Query.s = "SELECT c.classtype, s.FirstName, s.LastName" +
          " FROM courses As c" +
          " LEFT JOIN students As s ON r.customerid = s.id"+
          " Where s.name like 'Name 3%'"

I think your table designs need a little work. I don't think all text is a great idea. For instance id's should be at least integers and dates should be date/time stamps otherwise searching between dates will be difficult.

Yes I know these designs need work. I'm basing the current design on what was given to me from another example when I asked for it. I need to go through and clean this up a lot. As my experience increases so will my designs. I have already done some re-coding as a result of what I have learned so far.

Thank you for the example though.

Now.. Based on the above how does you take that join and extract the data and use it? I know about using the query and while / wend with each data point for each record. Is that how it's done? Or is the above the way it is done?

Dean

[swan]

bobobo's example above is good as well as pb's own example.

Code: Select all

If DatabaseQuery(0,query)
  While NextDatabaseRow(0)
    Debug "RecID:"+GetDatabaseString(0,0)
    Debug "RecREsult:"+GetDatabaseString(0,1)
    Debug ",StudId:"+GetDatabaseString(0,2)
    Debug ",StudName+"+GetDatabaseString(0,3)
    Debug "*************"
  Wend
  FinishDatabaseQuery(0)
Else
  If DatabaseError()<>try
    Debug DatabaseError()+query
    try=DatabaseError()
  EndIf
EndIf

[deanathpc]

bobobo's example above is good as well as pb's own example.

Code: Select all

If DatabaseQuery(0,query)
  While NextDatabaseRow(0)
    Debug "RecID:"+GetDatabaseString(0,0)
    Debug "RecREsult:"+GetDatabaseString(0,1)
    Debug ",StudId:"+GetDatabaseString(0,2)
    Debug ",StudName+"+GetDatabaseString(0,3)
    Debug "*************"
  Wend
  FinishDatabaseQuery(0)
Else
  If DatabaseError()<>try
    Debug DatabaseError()+query
    try=DatabaseError()
  EndIf
EndIf

O.K. So it's the same as I have been doing. Good.. Now I'll see if I can figure this all out and implement it. Thanks!

Dean

[deanathpc]

Thanks folks.. This is my query and works great. Just had to sit down with the above examples and play around a bit to understand them and I got it finally. Code is not a little quicker too as a result. A lot less in the SELECT portion helped.

Code: Select all

Query.s = "SELECT studentcoursehistory.id, studentcoursehistory.WrittenTest, studentcoursehistory.Overall, studentcoursehistory.StudentID, students.LastName, students.FirstName FROM studentcoursehistory LEFT JOIN students on studentcoursehistory.StudentID=students.StudentID WHERE studentcoursehistory.CourseID='" + GetGadgetText(#String_CourseID) + "' ORDER BY LastName ASC"

Like I said works like a champ and didn't break any of my other code so I'm a happy camper. Now off to do some more JOINs and see how it all works!

Dean

[RichAlgeni]

Be aware that using raw data from GetGadgetText() could leave your query susceptible to SQL Injection attacks. That's probably not a big deal for this query, but could be for others. Especially if you use raw data to login to a system.