Help with Inner join problem

[Guest]

The following query works fine as long as there is a value in the subID field, which is at the end of the inner join statement. If the subID is blank then no data is output, even though it is there. Try as I may, I can not get the right solution in the where statement to get the data to show without the subID. What am I missing?

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

select page_id, pageName, pages.content, pages.cat_id, pages.seo_title,pages.seo_desc,pages.seo_words,pages.h1_title,pages.pic,pages.brochure,pages.video,catagories.catagory,subcat.subID,subcat.sub_category

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

where pages.page_id = #page_id#

</cfquery>

[Carl Von Stetten · Accepted solution]

Rick,

Looks like you need a LEFT OUTER join to the "subcat" table, so that all records for "pages" and "catagories" are returned even if no matching "subcat" records exist.  INNER joins will only return selected records when the tables on both sides of the join statement have matching values, while a LEFT OUTER (sometimes also known simply as a LEFT join) join will return *all* selected records from the table(s) on the left side of the join statement regardless of whether there are matching records from the table on the right side of the join statement.  When there are no matching records from the table on the right side of the join statement, any columns in your SELECT clause that are from the table on the right side of the join statement will contain NULLs.

HTH,

-Carl V.

[Guest]

Carl,

Again, thank you. Using the left Join worked perfectly.

Rick