SQL inner join three tables

nskwortsow

Hi,

I have three tables I need to join. My db is normalized and I don't want to store redundant data.

Here is my query in normal SQL

SELECT * FROM
tblA,tblB,tblC
WHERE
tblA.f_ID = tblB.ID AND
tblB.f_ID = tblC.ID

In you documentation, I can only find info about direct inner JOINs between 2 tables, or joins between 2 tables and a multi-table.

How would you write out my above query in your API?

Thanks,

Nathan

allan

HI Nathan,

Unfortunately a three way join is not currently supported by the Editor PHP implementation that Editor ships with. I've added it to the feature list to be looked at for future development. Thanks for bringing this up.

Allan

nskwortsow

Hi Allan,
Oh oh. This is an important aspect of my application. My ERD will get very messy if I have to setup link tables everywhere.
Can you propose a workaround?
Kind regards,
Nathan

allan

At this time, no - there is no workaround with the Editor classes as they weren't designed for that kind of application. You could perhaps use some custom code to handle tables which work this way rather than using the pre-built Editor classes.

The classes were designed to cover the majority of cases, but unfortunately you've hit one of the cases outside of that.

Allan

slemoine

Hi Allan,

As the last post about inner_join-three-tables is 5 monthes old, can we expect that this will be soon implemented?

Would be so helpfull....

regards.

Stephane

allan

Hi Stephane,

I'm afraid not no. Extended JOIN functionality is currently on hold while other features for Editor 1.3 are being worked on. Adding additional flexibility to the joins is something that will be considered for 1.4 (which will be the latest half of this year), although how it is actually implemented has still to be defined.

Regards,
Allan

burnchar

Have you considered using a view with triggers?
A view can join an arbitrary number of tables. A trigger can then handle requests to insert/edit data.

On another note, I have used Editor in a project that joins an arbitrary number of tables and presents the results in a view. Rather than triggers, I have some fairly simple logic server-side to handle data changes. "For column 1 do this. For column 2, do that..."
The only difficulties were application-specific (in my case, a logging table separates Editor from the actual table data), but the data handling parts were pretty straight-forward.