Skip to main content
A
Anonymous
July 31, 2013
Answered

Help with Inner join problem

  • July 31, 2013
  • 1 reply
  • 662 views

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>

    This topic has been closed for replies.
    Correct answer Carl Von Stetten

    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.

    1 reply

    Carl Von Stetten
    Carl Von StettenCorrect answer
    Legend
    July 31, 2013

    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.

    A
    Anonymous
    August 1, 2013

    Carl,

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

    Rick

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices