SQL Joins

ashiers

Hi there,

I've been working with Editor getting to know its features, however I've got to the point where I'm trying to wrap my head around joins. This is an advanced topic I need to understand. I'm very confused as I examine the example on http://editor.datatables.net/release/DataTables/extras/Editor/examples/join.html. Seeing how the joins are instantiated in the PHP file doesn't help much when I'm trying to associate them all with an SQL select statement.

Seeing a simple SQL JOIN STATEMENT like: SELECT T1.*,T2.* FROM T1 LEFT JOIN T2 ON T1.i1 = T2.i2; is hard enough to understand where we are just dealing with two joined tables. But I can't imagine the complexity of joining four tables in one query. What does that look like?

I need to understand how a four table join is put together in an SQL statement. Would you be able to provide the SQL select statement for the above mentioned example so I can examine it and try to understand it? If you have any instructions in helping me to break it down, that would be a tremendous help.

Thanks,

Alan

allan

Hi Alan,

Have you read through this tutorial: http://editor.datatables.net/tutorials/php_join ? It basically describes the options available in Editor's PHP Join class. It isn't possible to do a three level deep join using that class at this time, although you can do as many two level joins as you want.

Joins, as you know, provide a massive amount of flexibility. The Editor PHP Join class is absolutely not designed to cope with every single Join use case - but rather 80% of them (following the 80/20 rule). Much more complex structures can b used with Editor, but you'd need to customise the server-side scripts rather than using the pre-built classes.

Regards,
Allan

ashiers

Yes, I read it. But as mentioned, I'm confused about how arrays are converted into SQL syntax. For instance:
Join::inst( 'child', 'object' )
->join( array( 'p_id', 'pl_id' ),
array( 'c_id', 'cl_id' ),
'link_table' )
->fields( // ... )

As part of a select statement, what does the condition look like? And where does the intermediary "link_table" fit into the join part of the statement?

SELECT ... FROM Table1 INNER JOIN [which table??] ON ??????????

I'm just guessing, I'll take my best shot, but does it go something like this?

Legend:
Table1 alias T1
link_table alias LT
Table2 alias T2

SELECT ... FROM Table1 AS T1 INNER JOIN link_table AS LT ON T1.p_id = LT.pl_id INNER JOIN Table2 AS T2 on LT.cl_id = T2.c_id;

Regards,

Alan

allan

This is the exact query that the library builds up based on your input:

[code]
SELECT users.p_id as _dte_pkey
FROM users as users
JOIN link_table ON users.p_id = link_table.plaid
JOIN child ON child.c_id = link_table.cl_id
[/code]

So yes, basically as you say!

Allan

ashiers

Thanks for the clarification, that helps.

Alan