Re: Joining a result set from four (4) tables

Because the logic structure of this software is in the front end
application rather than the database there is a strong need to keep the
naming of fields generic rather than specific, I am not using
pre-defined foreign keys at all. If I was building the database with a
purpose specific goal I would be doing what you say. I have found though
that when I label elements at different levels of the back end for one
purpose, they are less transportable in the long run. In this case the
naming conventions are actually stored in another table and applied as
aliases when needed. That way I can change the names and labels (for a
new client or industry) while the underlying structure remains the same.
I hope to increase interoperability in this way as well.

We all find ourselves in different situations and because of that, what works for one person, doesn't work for another - so I understand.  Good luck with the application and future queries.  Maybe you can use the existing structure of your application to help create a query builder so you can have it write a lot of your joins for you.  That should help avoid a lot of simply typos or mix-ups when hand writing your queries.

I am glad the queries worked for you.

-Aaron