How to use join table

From the following 3 diagrams, you may have a better understand of TLzJoin , TLzJoinList, TLzJoinItem, TLzJoinItemList which is used to represent join table in from clause.

You can check TSelectSqlStatement.JoinTables which is type of TLzJoinList to start fetching all those information.

Scenario 1

Select t1.f1
from my.table1 t1,my.table2 t2
where t1.f1 = t2.f1

After parsing this sql, information in TSelectSqlStatement.Tables is:

Tables.Count = 2;

Tables[0].TableName = 'table1';
Tables[0].TablePrefix = 'my';
Tables[0].TableAlias = 't1';

Tables[1].TableName = 'table2';
Tables[1].TablePrefix = 'my';
Tables[1].TableAlias = 't2';

And information in TSelectSqlStatement.JoinTables is:
JoinTables.Count = 2;

JoinTables.items[0].JoinTableType = jttTable; // the collection of join is table : my.table1
JoinTables.items[0].JoinTable = Tables[0];//my.table1
JoinTables.items[0].JoinItems.count = 0;

JoinTables.items[1].JoinTableType = jttTable; // the collection of join is table : my.table1
JoinTables.items[1].JoinTable = Tables[1];//my.table2
JoinTables.items[1].JoinItems.count = 0;

Scenario 2

Select t1.f1
from my.table1 t1
join my.table2 t2
on t1.f1 = t2.f1

After parsing this sql, information in TSelectSqlStatement.Tables is:

Tables.Count = 2;

Tables[0].TableName = 'table1';
Tables[0].TablePrefix = 'my';
Tables[0].TableAlias = 't1';

Tables[1].TableName = 'table2';
Tables[1].TablePrefix = 'my';
Tables[1].TableAlias = 't2';

everything is the same as scenario 1, Now, check the information in JoinTables:

JoinTables.Count = 1;

JoinTables.items[0].JoinTableType = jttTable; // the collection of join is table : my.table1
JoinTables.items[0].JoinTable = Tables[0];//my.table1
JoinTables.items[0].JoinItems.count = 1;

JoinTables.items[0].JoinItems[0].JoinItemTableType = jttTable;//the collection of this joinitem is table:my.table2
JoinTables.items[0].JoinItems[0].JoinItemTable = my.table2;
JoinTables.items[0].JoinItems[0].JoinType = sjtjoin;
JoinTables.items[0].JoinItems[0].JoinQualType = sjqOn;
JoinTables.items[0].JoinItems[0].JoinQual = AEXPRESSION ; //a custom expressoin point to t1.f1 = t2.f1

Scenario 3

select t1.f1
from my.table1 t1
 join (my.table2 t2
 left join my.table3 t3
 on t2.f1 = t3.f1) as joinalias1
 on t1.f1 = t2.f1;

After parsing this sql, information in Tables is:

Tables.Count = 3;

Tables[0].TableName = 'table1';
Tables[0].TablePrefix = 'my';
Tables[0].TableAlias = 't1';

Tables[1].TableName = 'table2';
Tables[1].TablePrefix = 'my';
Tables[1].TableAlias = 't2';

Tables[2].TableName = 'table3';
Tables[2].TablePrefix = 'my';
Tables[2].TableAlias = 't3';

Now, checking the information in JoinTables:

JoinTables.Count = 1;


JoinTables.items[0].JoinTableType = jttTable; // the collection of join is table : my.table1
JoinTables.items[0].JoinTable = Tables[0];//my.table1
JoinTables.items[0].JoinItems.count = 1;

JoinTables.items[0].JoinItems[0].JoinItemTableType = jttJoin;//the collection of this joinitem is join
JoinTables.items[0].JoinItems[0].JoinItemJoin = AJoin; // ajoin point to (my.table2 t2
left join my.table3 t3
on t2.f1 = t3.f1) as joinalias1

JoinTables.items[0].JoinItems[0].JoinType = sjtjoin;
JoinTables.items[0].JoinItems[0].JoinQualType = sjqOn;
JoinTables.items[0].JoinItems[0].JoinQual = AEXPRESSION ; //a custom expressoin point to t1.f1 = t2.f1


JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinTableType = jttTable;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinTable = Tables[1]; //my.table2
JoinTables.items[0].JoinItems[0].JoinItemJoin.Alias = joinalias1
JoinTables.items[0].JoinItems[0].JoinItemJoin.AliasWithAs = true;
JoinTables.items[0].JoinItems[0].JoinItemJoin.NestedLevel = 1;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems.count = 1;

JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinItemTableType = jttTable;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinItemTable = Tables[2]; //my.table3
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinType = sjtleft;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinQualType = jqtOn;
JoinTables.items[0].JoinItems[0].JoinItemJoin.JoinItems[0].JoinQual = AEXPRESSION ; //a custom expressoin point to t2.f1 = t3.f1

Scenario 4

select t1.f1
from my.table1 t1
 right join ((my.table2 t2
 left outer join my.table3 t3
 on (t2.f1 = t3.f2))
 left join (my.table4 t4
 full outer join my.table5 t5
 on (t4.f1 = t5.f1)) t4alias
 on (t4.b1 = t2.c1))
 on (t1.a1 = t3.b3);

After parsing this sql, information in Tables is:

Tables.Count = 5;

Tables[0].TableName = 'table1';
Tables[0].TablePrefix = 'my';
Tables[0].TableAlias = 't1';

Tables[1].TableName = 'table2';
Tables[1].TablePrefix = 'my';
Tables[1].TableAlias = 't2';

Tables[2].TableName = 'table3';
Tables[2].TablePrefix = 'my';
Tables[2].TableAlias = 't3';

Tables[3].TableName = 'table4';
Tables[3].TablePrefix = 'my';
Tables[3].TableAlias = 't4';

Tables[4].TableName = 'table5';
Tables[4].TablePrefix = 'my';
Tables[4].TableAlias = 't5';

Now, checking the information in JoinTables:

JoinTables.Count = 1;

JoinTables.items[0].JoinTableType = jttTable;
JoinTables.items[0].JoinTable = Tables[0]; //my.table1

JoinTables.items[0].JoinItems.count = 1;

JoinTables.items[0].JoinItems[0].JoinType = sjtrightjoin;
JoinTables.items[0].JoinItems[0].JoinQualType = sjqOn;
JoinTables.items[0].JoinItems[0].JoinQual = AEXPRESSION ; //a custom expressoin point to t1.a1 = t3.b3

JoinTables.items[0].JoinItems[0].JoinItemType = jttJoin;
JoinTables.items[0].JoinItems[0].JoinItemJoin = AJoin; // ((my.table2 t2
left outer join my.table3 t3
on (t2.f1 = t3.f2))
left join (my.table4 t4
full outer join my.table5 t5
on (t4.f1 = t5.f1)) t4alias
on (t4.b1 = t2.c1))



AJoin.NestedLevel = 1;
AJoin.JoinTableType = jttJoin;
AJoin.JoinJoin = ASubJoin; //(my.table2 t2
left outer join my.table3 t3
on (t2.f1 = t3.f2))

ASubJoin.NestedLevel = 1;
ASubJoin.JoinTableType = jttTable;
ASubJoin.JoinTable = Tables[1]; //my.table2
ASubJoin.JoinItems.count = 1;
ASubJoin.JoinItems.items[0].JoinType = sjtleftjoin;
ASubJoin.JoinItems.items[0].JoinQualType = jtqon;
ASubJoin.JoinItems.items[0].JoinQual = AExpression;//t2.f1 = t3.f2
ASubJoin.JoinItems.items[0].JoinItemTableType = jttTable;
ASubJoin.JoinItems.items[0].JoinItemTable = Tables[2]; //my.table3


AJoin.JoinItems.count = 1;

AJoin.JoinItems.items[0].JoinTableType = jttJoin;//again, this is a join
AJoin.JoinItems.items[0].JoinJoin = ASub2Join;//(my.table4 t4
full outer join my.table5 t5
on (t4.f1 = t5.f1)) t4alias

ASub2Join.Alias = t4alias;
ASub2Join.NestedLevel = 1;
ASub2Join.JoinTableType = jttTable;
ASub2Join.JoinTable = Tables[3]; //my.table4
ASub2Join.JoinItems.count = 1;
ASub2Join.JoinItems.items[0].JoinType = sjtfullOuter;
ASub2Join.JoinItems.items[0].JoinQualType = jtqon;
ASub2Join.JoinItems.items[0].JoinQual = AExpression;//t4.f1 = t5.f1
ASub2Join.JoinItems.items[0].JoinItemTableType = jttTable;
ASubJoin.JoinItems.items[0].JoinItemTable = Tables[4]; //my.table5

AJoin.JoinItems.items[0].JoinType = sjtleftouter;//left join
AJoin.JoinItems.items[0].JoinQualType = sjqOn;
AJoin.JoinItems.items[0].JoinQual = sjqOn;// t4.b1 = t2.c1

You may also check this demo:

http://www.sqlparser.com/dl/samples/jointabledetail.zip