How do I inner join 4 tables?

[rickclark54]

I have a page table that includes navigation IDs, catID, subcatID, I use an inner join for the tables.

Tables:

pages

categories

subcat

I have added subSubID for a third level on the nav menu. How do I add the fourth table to the cfquery?

I want to add: inner join subSubcat on pages.subSubID = subSubcat.subSubID to the table below.

<cfquery name="getPages" datasource="#application.database#">

select *

from (pages INNER JOIN catagories ON pages.cat_id = catagories.cat_id)

inner join subcat on pages.subID = subcat.subID

Where catagory = '#title#'

order by pages.subID asc, pages.ordID asc

</cfquery>

[EddieLotter]

Your question isn't a ColdFusion question (which is what these forums are for), however, you provide the answer in your own question.

You can't be asking where to add the inner join clause, right??

Since database questions can often be specific to a database engine, you would get the best responses on a database forum of the database engine of your choice.

[rickclark54]

Thanks Eddie. I understand that the question is a database question. However, in my limited experience with mySQL, doesn't coldfusion use a lot of its own hooks to make things work. For example saving an ajax variable to a mysql with Coldfusion uses a different approach than PHP does.

I did find my answer though, by quite a few trial and error attempts.

[EddieLotter]

The SQL query defined in a cfQuery tag is parsed by ColdFusion, but the resulting text is then passed as is to the database engine.

The only thing ColdFusion is doing to your example query is replace the characters #title# with a value, but other than that the query must be in the format that the database engine expects. ColdFusion will happily pass garbage to the database which will result in the database throwing an error.

You can also use ColdFusion logic inside the definition of a query, if you want to include a part of a query under certain circumstances, but again, once ColdFusion has parsed the definition, the resulting query text is passed directly to the database.

If necessary, you can create your queries in the mySQL Query Browser or mySQL Workbench to make sure they work as expected, then copy the text of the query to your ColdFusion script. Then you can replace parameters with ColdFusion variables. In fact, I would strongly recommend that you do it this way, it will save you a lot of time.

I hope I made things clearer, but if not, let me know and I will include some examples.