Skip to main content
A
Anonymous
May 29, 2011
Question

Inner join with if statement , how to combine these code ?

  • May 29, 2011
  • 1 reply
  • 1766 views

Hello everyone, I have 2 group of code here. This 2 group of code is working without error. But I face a problem , I need to group up this 2 group of code. Please take a look, thank you This is the first code i create.

SELECT r.Name , r.Restaurant_ID, f.feature, r.Price_Range, r.Cuisine_ID, c.Cuisine,
    s.State_ID, s.State, l.Location_ID, l.Area, l.State_ID, r.Name, r.Location_ID
FROM Restaurants r, Bridge1_Restaurant_Features b, Features f, Cuisine c, State s, Location l
        where 0=0
        AND b.Feature_ID = f.Feature_ID 
        AND b.Restaurant_ID = r.Restaurant_ID
        AND r.Cuisine_ID = c.Cuisine_ID 
        AND r.Location_ID = l.Location_ID
        AND l.State_ID = s.State_ID
        <cfif ARGUMENTS.Feature_ID IS NOT "">
        AND b.Feature_ID IN (#ARGUMENTS.Feature_ID#)
        </cfif>
        <cfif ARGUMENTS.Price_Range IS NOT "">
        AND r.Price_Range IN (#ARGUMENTS.Price_Range#)
        </cfif>
        <cfif ARGUMENTS.Cuisine IS NOT "">
        AND r.Cuisine_ID = (#ARGUMENTS.Cuisine#)
        </cfif>
        <cfif val(ARGUMENTS.LocationID2) IS #val(ARGUMENTS.StateID)#>
        AND l.State_ID = #val(ARGUMENTS.LocationID2)#
        <cfelse>
            AND l.Location_ID = #val(ARGUMENTS.LocationID2)#
        </cfif>

Then, I notice the feature_ID i need to use another logic to show the result. The code is like this

SELECT r.Restaurant_ID, r.Name, f.Feature
FROM   Restaurants r
INNER JOIN Bridge1_Restaurant_Features b ON b.Restaurant_ID = r.Restaurant_ID
INNER JOIN Features f ON b.Feature_ID = f.Feature_ID
INNER JOIN
(
     SELECT Restaurant_ID, COUNT(Feature_ID) AS FeatureCount
     FROM   Bridge1_Restaurant_Features
     <!--- find matching features --->
     WHERE  Feature_ID IN ( <cfqueryparam value="#ARGUMENTS.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )
    GROUP BY Restaurant_ID
    <!--- having ALL of the requested features --->
     HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(ARGUMENTS.Feature_ID)#" cfsqltype="cf_sql_integer">
) ck ON ck.Restaurant_ID = r.Restaurant_Id

I want combine this 2 group together. The second group have to replace

<cfif ARGUMENTS.Feature_ID IS NOT "">
    AND f.Feature_ID IN (#ARGUMENTS.Feature_ID#)
</cfif>

I try few way to group this 2 code, but fail to make it. The code i try is on below, it get error.

SELECT r.Name , r.Restaurant_ID, f.feature, r.Price_Range, r.Cuisine_ID, c.Cuisine,
        s.State_ID, s.State, l.Location_ID, l.Area, l.State_ID,  r.Location_ID
        FROM Restaurants r, Features f, Cuisine c, State s, Location l
        INNER JOIN Bridge1_Restaurant_Features b ON b.Restaurant_ID = r.Restaurant_ID
        INNER JOIN Features f ON b.Feature_ID = f.Feature_ID
        AND r.Cuisine_ID = c.Cuisine_ID 
        AND r.Location_ID = l.Location_ID
        AND l.State_ID = s.State_ID
        <cfif ARGUMENTS.Feature_ID IS NOT "">
        INNER JOIN
        (
             SELECT Restaurant_ID, COUNT(Feature_ID) AS FeatureCount
             FROM   Bridge1_Restaurant_Features
             <!--- find matching features --->
             WHERE  Feature_ID IN ( <cfqueryparam value="#ARGUMENTS.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )
            GROUP BY Restaurant_ID
            <!--- having ALL of the requested features --->
             HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(ARGUMENTS.Feature_ID)#" cfsqltype="cf_sql_integer">
        ) ck ON ck.Restaurant_ID = r.Restaurant_Id
        </cfif>
        <cfif ARGUMENTS.Price_Range IS NOT "">
        AND r.Price_Range IN (#ARGUMENTS.Price_Range#)
        </cfif>
        <cfif ARGUMENTS.Cuisine IS NOT "">
        AND r.Cuisine_ID = (#ARGUMENTS.Cuisine#)
        </cfif>
        <cfif val(ARGUMENTS.LocationID2) IS #val(ARGUMENTS.StateID)#>
        AND l.State_ID = #val(ARGUMENTS.LocationID2)#
        <cfelse>
            AND l.Location_ID = #val(ARGUMENTS.LocationID2)#
        </cfif>

This topic has been closed for replies.

1 reply

A
Adam Cameron.
Inspiring
May 29, 2011
I try few way to group this 2 code, but fail to make it. The code i try is on below, it get error.

OK, don't just tell us you get an error: tell is what the error is.  Also, with errors in SQL the error message usually dumps out the SQL that was passed to the DB.  Usually with this sort of thing one's logic unfolds in such a way that the resultant SQL is not valid... and it's usually fairly easy to spot from the final SQL string being passed.

--

Adam

A
Anonymous
May 29, 2011

Sorry Adam.

Error Executing Database Query.

Unknown column 'r.Restaurant_ID' in 'on clause'
The error occurred in E:\wamp\www\Search\Search.cfc: line 124
Called from E:\wamp\www\Search\Advance_Search.cfm: line 11
Called from E:\wamp\www\Search\Search.cfc: line 124
Called from E:\wamp\www\Search\Advance_Search.cfm: line 11
122 :           AND l.State_ID = #val(ARGUMENTS.LocationID2)#
123 :           <cfelse>
124 :                AND l.Location_ID = #val(ARGUMENTS.LocationID2)#
125 :           </cfif>
126 :           

But i guess the error is from

INNER JOIN

          (

               SELECT Restaurant_ID, COUNT(Feature_ID) AS FeatureCount

               FROM   Bridge1_Restaurant_Features

               <!--- find matching features --->

               WHERE  Feature_ID IN ( <cfqueryparam value="#ARGUMENTS.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )

              GROUP BY Restaurant_ID

              <!--- having ALL of the requested features --->

               HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(ARGUMENTS.Feature_ID)#" cfsqltype="cf_sql_integer">

          ) ck ON ck.Restaurant_ID = r.Restaurant_Id

A
Adam Cameron.
Inspiring
May 29, 2011

OK, we're making some headway.  But - like I said - in the error message there will be the SQL string that CF sent to the DB.  What is that SQL?  Not the CF code that generates the SQL, but the actual final SQL that CF sends to the DB.

--

Adam

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