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

Report · May 29, 2011

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>

Report · 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

Report · 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
               
               WHERE Feature_ID IN ( <cfqueryparam value="#ARGUMENTS.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )
              GROUP BY Restaurant_ID
              
               HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(ARGUMENTS.Feature_ID)#" cfsqltype="cf_sql_integer">
          ) ck ON ck.Restaurant_ID = r.Restaurant_Id

Report · 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

Report · May 29, 2011

Before i adding the code for SQL above i show , everything i working fine.

After i change the SQL form , only get error message.

Report · May 30, 2011

OK, I know there's a bit of a language barrier thing going on here, but you need to listen/read what I say, and provide the answers to the questions I ask you. Otherwise we're not going to get anywhere.

Your error message will look something like this:

Error Executing Database Query.
Table 'scratch.tbl_data' doesn't exist

The error occurred in C:\webroots\shared\junk\crap.cfm: line 6

4 :     WHERE
5 :     <cfif true>
6 :             someCol = <cfqueryparam value="#randRange(1, 6)#">
7 :     </cfif>
8 : </cfquery>

VENDORERRORCODE       1146
SQLSTATE       42S02
SQL        SELECT * FROM tbl_data WHERE someCol = (param 1)
DATASOURCE       scratch
Resources:

    * Check the ColdFusion documentation to verify that you are using the correct syntax.
    * Search the Knowledge Base to find a solution to your problem.

What I need you to do is to post the bit that shows the SQL that was passed to the DB. In my example, it's the bit:

SQL SELECT * FROM tbl_data WHERE someCol = (param 1)

I don't want to know (nor do I care) what changes you've made to the code, what I want to see is that SQL. That's what you should be looking at.

So... can you please post that bit.

Cheers.

--

Adam

Report · May 30, 2011

And, thank-you once again Jive Forums for removing all my formatting from that post. Wonderful. You rock.

😕

--

Adam

Report · May 30, 2011

Yeah Adam I am listen to you ! But Finally i solve it.

This is the code i solve .

SELECT r.Restaurant_ID, r.Name, f.Feature, r.Price_Range, r.Cuisine_ID, c.Cuisine,
                    l.Location_ID, l.Area, s.State
          FROM   Restaurants r
          <cfif ARGUMENTS.Feature_ID IS NOT "">
          INNER JOIN
          (
               SELECT Restaurant_ID, COUNT(Feature_ID) AS FeatureCount
               FROM   Bridge1_Restaurant_Features
               
               WHERE Feature_ID IN ( <cfqueryparam value="#ARGUMENTS.Feature_ID#" cfsqltype="cf_sql_integer" list="true"> )
              GROUP BY Restaurant_ID
              
               HAVING COUNT(Feature_ID) = <cfqueryparam value="#listLen(ARGUMENTS.Feature_ID)#" cfsqltype="cf_sql_integer">
          ) ck ON ck.Restaurant_ID = r.Restaurant_Id
          </cfif>
          INNER JOIN Location l ON r.Location_ID = l.Location_ID
          INNER JOIN State s ON l.State_ID = s.State_ID
          INNER JOIN Cuisine c ON r.Cuisine_ID = c.Cuisine_ID
          INNER JOIN Bridge1_Restaurant_Features b ON b.Restaurant_ID = r.Restaurant_ID
          INNER JOIN Features f ON b.Feature_ID = f.Feature_ID
          <cfif ARGUMENTS.Cuisine IS NOT "">
          AND r.Cuisine_ID = (#ARGUMENTS.Cuisine#)
          </cfif>
          <cfif ARGUMENTS.Price_Range IS NOT "">
          AND r.Price_Range IN (#ARGUMENTS.Price_Range#)
          </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>

Is it the code can be improve more ?

Report · May 30, 2011

Cool: glad you go there. Next time you have a problem though, always check that SQL bit that shows the SQL actually being executed: that is the easiest way to spot where something is going wrong.

Not knowing your DB schema I can't really comment too much on whether that SQL is ideal, but it looks OK. There are a few dynamic values that you're not CFQUERYPARAMing though. That's the only observation I would make.

One thing to look for is how well the SQL executes. What's the execution time on that? What about when the server is under load (you can simulate load with JMeter or something like that).

Also probably run a profile on it to see whether you've got all the best indexes on your PK/FK relationships and stuff like that.

--

Adam

Report · May 30, 2011

Adam, thank you again, I'm appreciate you always share your knowledge with me!

I will remember your suggestion for futher use

Really , i'm glad to have your help.