Doing Table Joins

[Fredrick Ughimi]

Hello,

Code Select Expand


slSel "SELECT * from tblPersonnel, tblPayroll WHERE tblPersonnel.PIN = tblPayroll.PIN;"


I get error -13 - Invalid column name or number.

I think I have hit the same problem Joe Byrne had here:
http://www.sqlitening.com/support/index.php?topic=2601.msg13025#msg13025

The solution wasn't shown on the thread.

TblPersonnel was created like this:

Code Select Expand


slExe Build$("Create Table If Not Exists tblPersonnel(PIN Integer Primary Key, Surname, Othernames, Sex,", _
      "BirthDate, Age, FirstAppDate, ConfirmDate, RetireDate, Rank, CONTISS, HighQual, DeptSch, [Union], GLS, [Address],", _
      "LGA, [State], MStatus, [Shift], KinName, KinPhoneNo, KinEmail, KinAddress, KinRelate, PixPath, Remark)")   


TblPayroll was created like this:

Code Select Expand


slExe Build$("Create Table If Not Exists tblPayroll(PIN INTEGER Unique, Bank, Salary, Arrears, Rent, [Shift],", _
         "ActAllow, GrossPay, Tax, ASUCHT, NASU, SalaryAdv, BankLoan, [PGLoan], CorpLoan, [CorpCont],", _
         "Welfare, [Refunds], OtherDedu, [NetPay])")

   
Any suggestion greatly be appreciated.

[Fred Meier]

Yes, the posted thread addresses your problem --- your select statement is returning duplicate column names.  That post also has the solution:

The solution is to assign a different column name in the Select statement as follows:
Note that the "as" is optional.

Check out the 'D' modchar that is now available in slSel:

      D  = Allow duplicate column names. Not recommended if using slFN or slFNX.
           because you will always get the first value returned.  SQLite does
           not normally return qualified column names.  SQLite will return C1
           twice if you Select T1.C1, T2.C1. So the solution is to alias one of
           them with the As clause as follows Select T1.C1, T2.C1 as C1Again.
           There is a Pragma called "full_column_names" which forces SQLite to
           return qualified names, but does not seem to work if you Select *.
           Read up on it and use if you like.  I like using an alias because it
           is less code and more clear.

Your are using 'Select *' which is telling SQLite to return 47 fields of
data for each row.  Do you really need all 47 fields?  I try to avoid
using *.  You don't want to ask for any extra columns, especially If you
are running remote.  Also, if you don't use * then you can assign unique
names to each column using the 'as' clause. 

[Fredrick Ughimi]

Hello Fred,

You are right. Had to use the INNER JOIN explicitly to make it work though.

Code Select Expand


slSel "SELECT " & _
            "   tblPersonnel.PIN as PersonnelPIN," & _
            "   tblPayroll.PIN as PayrollPIN," & _
            "   tblPersonnel.Surname," & _
            "   tblPersonnel.Othernames," & _
            "   tblPersonnel.GLS," & _
            "   Bank," & _
            "   Salary," & _
            "   Arrears," & _
            "   Rent," & _
            "   ShiftAllow," & _
            "   ActAllow," & _
            "   GrossPay," & _
            "   Tax," & _
            "   ASUCHT," & _
            "   NASU," & _
            "   SalaryAdv," & _
            "   BankLoan," & _
            "   PGLoan," & _
            "   CorpLoan," & _
            "   CorpCont," & _
            "   Welfare," & _
            "   Refunds," & _
            "   OtherDedu," & _
            "   NetPay" & _ 
            " FROM" & _
            "  tblPersonnel" & _
            "  INNER JOIN tblPayroll ON (PersonnelPIN =PayrollPIN)" & _
            " ORDER BY" & _
            "   PersonnelPIN," & _
            "   tblPersonnel.Surname"


I don't quite understand how to explicitly specify the individual fields using the earlier 'join' (slSel "SELECT * from tblPersonnel, tblPayroll WHERE tblPersonnel.PIN = tblPayroll.PIN;"). Glad if you could throw more light on this or better still give the equivalent of the working code above. Could be handy in the future.

Kind regards,

[Fredrick Ughimi]

Hello Fred,

Never mind. I get it now. Its something like this:

Code Select Expand


slSel "SELECT tblPersonnel.PIN as PersonnelPIN, tblPersonnel.Surname, tblPersonnel.Othernames," & _
                "tblPayroll.PIN As PayrollPIN, tblPayroll.Bank, tblPayroll.Salary From tblPersonnel, tblPayroll WHERE PersonnelPIN = PayrollPIN;"


Which is even faster? The Join with expicit use of INNER JOIN and the join without explicitly using the JOIN statement?

Kind regards,

[Fred Meier]

Select F1,F2 from T1 inner join T2 on F3 = F4   ("explicit join notation")
         is same as
Select F1,F2 from T1,T2 on F3 = F4              ("implicit join notation")
         is same as
Select F1,F2 from T1,T2 where F3 = F4         ("implicit join notation")

Results and speed should be the same.