[Skip to main content](#vessel-content)

#   SQL Error on price filter

##

- **[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")**
- [![ced1870's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/29000-ced1870 "View ced1870's Profile") Topic Author
- [ Offline](/forum/user/29000-ced1870)

Less More

- Posts: 73
- Thank you received: 3
-
-

12 years 1 month ago [\#174241](#174241) by [ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")  *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174241 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")* was created by *[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")*

-- HikaShop version -- : 3.3.3
-- Joomla version -- : 3.3.4
-- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product_id' in 'on clause' SQL=SELECT DISTINCT b.product_id,b.compatibility, pprice.price_value FROM xzs4r_hikashop_product_category AS a LEFT JOIN (SELECT * FROM xzs4r_hikashop_product AS product1 LEFT JOIN xzs4r_hikashop_price AS price1 ON product1.product_id=price1.price_product_id LEFT JOIN xzs4r_hikashop_price AS pprice ON b.product_id=pprice.price_product_id WHERE (price1.price_access = 'all' OR price1.price_access LIKE '%,8,%' OR ISNULL(price1.price_access)) AND product1.product_type='main' GROUP BY product1.product_id ORDER BY price1.price_min_quantity ASC) AS b ON a.product_id=b.product_id LEFT JOIN xzs4r_hikashop_price AS pprice ON b.product_id=pprice.price_product_id WHERE b.product_published=1 AND b.product_type = 'main' AND a.category_id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product_quantity!=0 AND (b.product_access = 'all' OR b.product_access LIKE '%,8,%') AND ( ( case when b.price_currency_id IS NULL then 0 when b.price_currency_id = '1' then b.price_value when b.price_currency_id IS NULL then 0 when b.price_currency_id = '2' then (b.price_value/1) end ) &gt;= 1 ) AND ( case when b.price_currency_id IS NULL then 0 when b.price_currency_id = '1' then b.price_value when b.price_currency_id IS NULL then 0 when b.price_currency_id = '2' then (b.price_value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product_created DESC

Hi again
I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100
As soon as I choose apply on "Price", it gives me an error

> 1054 - Unknown column 'b.product\_id' in 'on clause' SQL=SELECT DISTINCT b.product\_id,b.compatibility, pprice.price\_value FROM xzs4r\_hikashop\_product\_category AS a LEFT JOIN (SELECT \* FROM xzs4r\_hikashop\_product AS product1 LEFT JOIN xzs4r\_hikashop\_price AS price1 ON product1.product\_id=price1.price\_product\_id LEFT JOIN xzs4r\_hikashop\_price AS pprice ON b.product\_id=pprice.price\_product\_id WHERE (price1.price\_access = 'all' OR price1.price\_access LIKE '%,8,%' OR ISNULL(price1.price\_access)) AND product1.product\_type='main' GROUP BY product1.product\_id ORDER BY price1.price\_min\_quantity ASC) AS b ON a.product\_id=b.product\_id LEFT JOIN xzs4r\_hikashop\_price AS pprice ON b.product\_id=pprice.price\_product\_id WHERE b.product\_published=1 AND b.product\_type = 'main' AND a.category\_id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product\_quantity!=0 AND (b.product\_access = 'all' OR b.product\_access LIKE '%,8,%') AND ( ( case when b.price\_currency\_id IS NULL then 0 when b.price\_currency\_id = '1' then b.price\_value when b.price\_currency\_id IS NULL then 0 when b.price\_currency\_id = '2' then (b.price\_value/1) end ) &gt;= 1 ) AND ( case when b.price\_currency\_id IS NULL then 0 when b.price\_currency\_id = '1' then b.price\_value when b.price\_currency\_id IS NULL then 0 when b.price\_currency\_id = '2' then (b.price\_value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product\_created DESC

If I set apply on "Weight", I don't have any error
Let me know if you want me to test something to debug
Regards
CEd

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

- **[Jerome](/forum/user/80-jerome "View Jerome's Profile")**
- [![Jerome's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar80.png)](/forum/user/80-jerome "View Jerome's Profile")
- [ Offline](/forum/user/80-jerome)
- HikaShop core developer

Less More

- Posts: 26401
- Thank you received: 4081
-
- [](http://obsi.dev)
-

12 years 1 month ago [\#174244](#174244) by [Jerome](/forum/user/80-jerome "View Jerome's Profile")  Replied by *[Jerome](/forum/user/80-jerome "View Jerome's Profile")* on topic *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174244 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")*

Hi,

Please see this post
[ www.hikashop.com/forum/filters-search/87...iltering.html#174179](http://www.hikashop.com/forum/filters-search/871750-no-results-on-filtering.html#174179)

Regards,

---

Jerome - Obsidev.com
HikaMarket &amp; HikaSerial developer / HikaShop core dev team.

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

- **[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")**
- [![ced1870's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/29000-ced1870 "View ced1870's Profile") Topic Author
- [ Offline](/forum/user/29000-ced1870)

Less More

- Posts: 73
- Thank you received: 3
-
-

12 years 1 month ago [\#174278](#174278) by [ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")  Replied by *[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")* on topic *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174278 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")*

I have taken the new file , replaced and it works for me.
Now I see that when I search, for example with no filter filled, it returns nothing. Or it should return all ? as no filter is set
for example :
- dropdown (version) - no value set, I leave the default text which is the filter name
- text, idem dropdown
- price ![:I](/neutral.gif ":I") set from 0 to 0 to have all items from all versions that are free.

This does not work. Am I missing or misunderstanding someting ?
CEd

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

- **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")**
- [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile")
- [ Offline](/forum/user/63-nicolas)

Less More

- Posts: 86576
- Thank you received: 14320
-
-

12 years 1 month ago [\#174395](#174395) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile")  Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174395 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")*

Hi,

It's probably just a matter of setting up the filters/the filter module properly.
You need to have a products listing menu item which lists all your products. Then, you need to enter that id in the options of your hikashop filter module with the "force redirect" option turned on. That way, when you use the filters displayed by the filter module, the system will redirect you to that products listing menu and filter the products of that menu with your selected filter(s). If you provide a wrong id to the module (if it's not a products listing with all the products), the system won't have anything to filter and thus you won't get any result.

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

- **[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")**
- [![ced1870's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/29000-ced1870 "View ced1870's Profile") Topic Author
- [ Offline](/forum/user/29000-ced1870)

Less More

- Posts: 73
- Thank you received: 3
-
-

12 years 1 month ago [\#174474](#174474) by [ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")  Replied by *[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")* on topic *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174474 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")*

I have checked and the module option is set to my menu item "105" with force redirect
My item 105 is the main link item to "products listing"
Then I have tried to filter from 0 to 0 euros, &gt;&gt; no result. Then I delete the price filter and click on filter again and there is no more items to show. ![:(](/media/kunena/emoticons/22.png ":(")

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

- **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")**
- [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile")
- [ Offline](/forum/user/63-nicolas)

Less More

- Posts: 86576
- Thank you received: 14320
-
-

12 years 1 month ago [\#174573](#174573) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile")  Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174573 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")*

Hi,

Can you provide a link to the page so that we can see the issue ?

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

- **[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")**
- [![ced1870's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/29000-ced1870 "View ced1870's Profile") Topic Author
- [ Offline](/forum/user/29000-ced1870)

Less More

- Posts: 73
- Thank you received: 3
-
-

12 years 1 month ago [\#174584](#174584) by [ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")  Replied by *[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")* on topic *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174584 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")*

themes(point)joomlack.fr

let me know when you have checked so that I can delete this url as it is a temporary website ![:)](/media/kunena/emoticons/3.png ":)")

Test procedure :
1/ select compatibility joomla3
&gt;&gt; 2 results, 1 free+1 paid

2/ set the price to 0-0
&gt;&gt; no result (should have 1)

3/ reset the price an compatibility (in the dropdown click on the word "compatibility")
&gt;&gt; no result (should return all)

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

- **[nicolas](/forum/user/63-nicolas "View nicolas's Profile")**
- [![nicolas's Avatar](https://www.hikashop.com/media/kunena/avatars/resized/size144/users/avatar63.png?1793428657)](/forum/user/63-nicolas "View nicolas's Profile")
- [ Offline](/forum/user/63-nicolas)

Less More

- Posts: 86576
- Thank you received: 14320
-
-

12 years 1 month ago [\#174695](#174695) by [nicolas](/forum/user/63-nicolas "View nicolas's Profile")  Replied by *[nicolas](/forum/user/63-nicolas "View nicolas's Profile")* on topic *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174695 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")*

Hi,

Ah ok. I thought that the problem was that the products were not displaying at all when using the filters but apparently it's just in some specific cases. So the problem doesn't come from the menu item id option. It's something about the filtering query which doesn't take into account the products without prices.
Could you turn on the "debug mode" option of the Joomla configuration and provide the MySQL on the hikashop_product table that appears at the bottom of the page in the debug area in the results page ?
That would help us understand why this is happening.

Regarding the 3rd test case, that's something which should be fixed in next version and here is the thread about it:
[ www.hikashop.com/support/forum/filters-s...iltering.html#174257](http://www.hikashop.com/support/forum/filters-search/871750-no-results-on-filtering.html#174257)

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

- **[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")**
- [![ced1870's Avatar](https://www.hikashop.com/media/kunena/core/svg/person.svg)](/forum/user/29000-ced1870 "View ced1870's Profile") Topic Author
- [ Offline](/forum/user/29000-ced1870)

Less More

- Posts: 73
- Thank you received: 3
-
-

12 years 1 month ago [\#174769](#174769) by [ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")  Replied by *[ced1870](/forum/user/29000-ced1870 "View ced1870's Profile")* on topic *[SQL Error on price filter](/forum/45-filters-search/872071-sql-error-on-price-filter#174769 "-- HikaShop version -- : 3.3.3 -- Joomla version -- : 3.3.4 -- Error-message(debug-mod must be tuned on) -- : 1054 - Unknown column 'b.product&#95;id' in 'on clause' SQL=SELECT DISTINCT b.product&#95;id,b.compatibility, pprice.price&#95;value FROM xzs4r&#95;hikashop&#95;product&#95;category AS a LEFT JOIN (SELECT &#42; FROM xzs4r&#95;hikashop&#95;product AS product1 LEFT JOIN xzs4r&#95;hikashop&#95;price AS price1 ON product1.product&#95;id=price1.price&#95;product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE (price1.price&#95;access = 'all' OR price1.price&#95;access LIKE '%,8,%' OR ISNULL(price1.price&#95;access)) AND product1.product&#95;type='main' GROUP BY product1.product&#95;id ORDER BY price1.price&#95;min&#95;quantity ASC) AS b ON a.product&#95;id=b.product&#95;id LEFT JOIN xzs4r&#95;hikashop&#95;price AS pprice ON b.product&#95;id=pprice.price&#95;product&#95;id WHERE b.product&#95;published=1 AND b.product&#95;type = 'main' AND a.category&#95;id IN (12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,2) AND b.product&#95;quantity!=0 AND (b.product&#95;access = 'all' OR b.product&#95;access LIKE '%,8,%') AND ( ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) &gt;= 1 ) AND ( case when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '1' then b.price&#95;value when b.price&#95;currency&#95;id IS NULL then 0 when b.price&#95;currency&#95;id = '2' then (b.price&#95;value/1) end ) BETWEEN 0 AND 0 ORDER BY b.product&#95;created DESC  Hi again I'm using the filters and I have set a filter for Price, type cursor, min - max: 0 - 100 As soon as I choose apply on &quot;Price&quot;, it gives me an error  If I set apply on &quot;Weight&quot;, I don't have any error Let me know if you want me to test something to debug Regards CEd")*

I have enabled the debug mode so you can check and find what you need ![:)](/media/kunena/emoticons/3.png ":)")

> Regarding the 3rd test case, that's something which should be fixed in next version and here is the thread about it:
> [ www.hikashop.com/support/forum/filters-s...iltering.html#174257](http://www.hikashop.com/support/forum/filters-s...iltering.html#174257)

This fixed the issue !

Please [Log in](/login?return=aHR0cHM6Ly93d3cuaGlrYXNob3AuY29tL2ZvcnVtLzQ1LWZpbHRlcnMtc2VhcmNoLzg3MjA3MS1zcWwtZXJyb3Itb24tcHJpY2UtZmlsdGVyP3RtcGw9bWFya2Rvd24=) or [Create an account](/component/users/registration?Itemid=101) to join the conversation.

Board Categories HikaShop Install &amp; Update Product &amp; Category Display Content Creation Sales and Orders Orders management Reports &amp; Statistics Customers Checkout Payment Methods Shipping Methods Import &amp; Export (CSV, XLS…) Specific Features Affilation Taxes Wishlist Vote Carousel &amp; Slider Mass Actions Filters &amp; Search Discount &amp; Coupon Feature Request Integrations Showcase General Talk about HikaShop Development HikaShop 3.0 Beta Translation Commercial Jobs HikaShop Components HikaMarket Market: Feature requests Market: How to? Market: Bug Report HikaSerial General talk about HikaSerial Serial: Bug Report Serial: How to? Support en Français de HikaSerial (French Support) HikaSubscription HikaAuction Auction: Feature requests Auction: How to? Auction: Bug report Hikashop around the world Support en Français (French Support) Russian FAQ. Курс выживания с Hikashop. Частые вопросы и о Расширения Hikashop Наши сайты на Hikashop 日本語版サポート(Japanese Support) Thai German Archives Bug Report How to?

[](/forum/topics/feed/mode-topics?format=feed&type=rss)

Time to create page: 0.070 seconds

[Powered by](/forum/credits) [Kunena Forum](https://www.kunena.org)
