• Welcome to SQLitening Support Forum. Please login.
 
May 24, 2022, 12:11:42 AM

News:

Welcome to the SQLitening support forums!


Compound Query?

Started by Fredrick Ughimi, December 06, 2014, 06:49:53 AM

Previous topic - Next topic

Fredrick Ughimi

Hello,

Is there an easy way to Join 5 Tables?  Is Compound Query the right candidate for it? if it is, its not very clear to me.

Best regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

May be nested Inner Joins would do the majic? Looking good with 3 tables already. Lets see how it does for 5 tables.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

The table names can just be listed after the FROM.
If duplicate column names exist then an alias for the column name can be named using "AS".
I believe SQLitening has a switch for using duplicate column names, but alias works.  See first example.
I  like to use unique columns names when possible.


FUNCTION PBMAIN () AS LONG'
  DIM sRecordSet() AS STRING
  slopen "sample.db3","C"                   'open database or create
  slexe "drop table if exists t1"           'drop table t1 if it exists
  slexe "drop table if exists t2"           'drop table t2 if it exists
  slexe "create table t1(c1)"               'create table t1 with column c1
  slexe "create table t2(c1)"               'create table t2 with column c1
  slexe "insert into t1 values('Fredrick')" 'insert record into table t1
  slexe "insert into t2 values('Fredrick')" 'insert same record into table t2
  slselAry "select t1.c1 as x1,t2.c1 as x2 from t1,t2 where x1=x2",sRecordSet(),"Q9"
  ? JOIN$(sRecordSet(),$CRLF),,"Duplicate columns so using alias"

  slexe "drop table if exists t1"          'drop table t1 if it exists
  slexe "drop table if exists t2"          'drop table t2 if it exists
  slexe "create table t1(c1)"              'create table t1 with column c1
  slexe "create table t2(c2)"              'create table t2 with UNIQUE column name
  slexe "insert into t1 values('CJ')"      'insert value into table t1
  slexe "insert into t2 values('CJ')"
  slselAry "select c1,c2 from t1,t2 where c1=c2",sRecordSet(),"Q9"
  ? JOIN$(sRecordSet(),$CRLF),,"Unique column names"

END FUNCTION

cj

December 06, 2014, 12:07:47 PM #3 Last Edit: December 06, 2014, 12:10:59 PM by cj
More info.  I used option 3.

In the help file under Tutorials and Training -  Duplicate Column Names Raises Error 13

We will use the following two tables as examples:
   slExe "Create table T1(F1,F2,F3)   
   slExe "Create table T2(F1,F2)


If you join these two tables as follows:
slSel "Select T1.F1, T2.F1 from T1, T2"
You will receive error -13 Invalid column name or number.


You are requesting F1 twice.  Qualifying if with the table name makes SQLite happy but presents a problem for SQLitening as follows. 


SQLitening asks SQLite for the column names after the Select statement is prepared.  SQLite by default only returns the unqualified names(no table qualifier), therefore returning F1 twice.  SQLitening will raise an error because later when you ask for the value of F1 using slFN/slFNX it would not know which one to return. 


There are several ways to handle this situation.


1.  Add the "D" ModChar to the slSel which will allow duplicate column names. You would have to use slF/slFX.  Do no use slFN/slFNX because you will always get the first value returned. 


2.  Set the Pragma call "full_column_names=1".  This Pragma will have SQLite return qualified column names.  You can now use slFN/slFNX with the qualified column names.  Note that this does not work with Select *. 


3.  Assign a different column name to the duplicate ones by using the 'As' phrase as follows:
slSel "Select T1.F1 as X1, T2.F1 as X2 from T1, T2"
Now you would use X1 and X2 in you slFN/slFNX statements.  I like this one the best but any of the three will work. 






Debug Aid:


If you get error -13 and don't know which column is duplicated then add the "D" ModChar to your slSel and follow that with slGetColumnName.  This will display all the column names SQLite is returning


slSel "Select T1.F1, T2.F1 from T1, T2"
?slGetColumnName
 

Fredrick Ughimi

Hello CJ,

Thanks a lot for your response. Problem solved! I never knew one can do up to five joins with SQLite. Is there a limit?

Best regards,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

December 08, 2014, 04:41:06 PM #5 Last Edit: December 08, 2014, 04:43:00 PM by cj
Glad it helped.
I saw the limit in the SQLite documentation.  Guessing 24, but will have to look it up.

64
https://www.sqlite.org/limits.html

Fredrick Ughimi

Thank you for the info and the link.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet