Databases
StackOverflow2013
Postsdbo
POInner join with if statement, group
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POInner join with if statement, group
    text
    copied!<p>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.</p> <pre><code>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 &lt;cfif ARGUMENTS.Feature_ID IS NOT ""&gt; AND f.Feature_ID IN (#ARGUMENTS.Feature_ID#) &lt;/cfif&gt; &lt;cfif ARGUMENTS.Price_Range IS NOT ""&gt; AND r.Price_Range IN (#ARGUMENTS.Price_Range#) &lt;/cfif&gt; &lt;cfif ARGUMENTS.Cuisine IS NOT ""&gt; AND r.Cuisine_ID = (#ARGUMENTS.Cuisine#) &lt;/cfif&gt; &lt;cfif val(ARGUMENTS.LocationID2) IS #val(ARGUMENTS.StateID)#&gt; AND l.State_ID = #val(ARGUMENTS.LocationID2)# &lt;cfelse&gt; AND l.Location_ID = #val(ARGUMENTS.LocationID2)# &lt;/cfif&gt; </code></pre> <p>Later then, I notice the feature_ID i need to use another logic to show the result. The code is like this </p> <pre><code>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 &lt;!--- find matching features ---&gt; WHERE Feature_ID IN ( &lt;cfqueryparam value="#ARGUMENTS.Feature_ID#" cfsqltype="cf_sql_integer" list="true"&gt; ) GROUP BY Restaurant_ID &lt;!--- having ALL of the requested features ---&gt; HAVING COUNT(Feature_ID) = &lt;cfqueryparam value="#listLen(ARGUMENTS.Feature_ID)#" cfsqltype="cf_sql_integer"&gt; ) ck ON ck.Restaurant_ID = r.Restaurant_Id </code></pre> <p>I want combine this 2 group together. The second group have to replace </p> <pre><code>&lt;cfif ARGUMENTS.Feature_ID IS NOT ""&gt; AND f.Feature_ID IN (#ARGUMENTS.Feature_ID#) &lt;/cfif&gt; </code></pre> <p>I try few way to group this 2 code, but fail to make it. The code i try is on below, it get error. </p> <pre><code>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 &lt;cfif ARGUMENTS.Feature_ID IS NOT ""&gt; INNER JOIN ( SELECT Restaurant_ID, COUNT(Feature_ID) AS FeatureCount FROM Bridge1_Restaurant_Features &lt;!--- find matching features ---&gt; WHERE Feature_ID IN ( &lt;cfqueryparam value="#ARGUMENTS.Feature_ID#" cfsqltype="cf_sql_integer" list="true"&gt; ) GROUP BY Restaurant_ID &lt;!--- having ALL of the requested features ---&gt; HAVING COUNT(Feature_ID) = &lt;cfqueryparam value="#listLen(ARGUMENTS.Feature_ID)#" cfsqltype="cf_sql_integer"&gt; ) ck ON ck.Restaurant_ID = r.Restaurant_Id &lt;/cfif&gt; &lt;cfif ARGUMENTS.Price_Range IS NOT ""&gt; AND r.Price_Range IN (#ARGUMENTS.Price_Range#) &lt;/cfif&gt; &lt;cfif ARGUMENTS.Cuisine IS NOT ""&gt; AND r.Cuisine_ID = (#ARGUMENTS.Cuisine#) &lt;/cfif&gt; &lt;cfif val(ARGUMENTS.LocationID2) IS #val(ARGUMENTS.StateID)#&gt; AND l.State_ID = #val(ARGUMENTS.LocationID2)# &lt;cfelse&gt; AND l.Location_ID = #val(ARGUMENTS.LocationID2)# &lt;/cfif&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload