Highlighted

How do I inner join 4 tables?

New Here ,
Sep 16, 2015

Copy link to clipboard

Copied

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>

Views

964

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

How do I inner join 4 tables?

New Here ,
Sep 16, 2015

Copy link to clipboard

Copied

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>

Views

965

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 16, 2015 0
Advocate ,
Sep 17, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 17, 2015 0
New Here ,
Sep 20, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 20, 2015 0
Advocate ,
Sep 21, 2015

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Sep 21, 2015 1