Reputation: 709
I'm trying to run this query:
SELECT
gc_category_products.product_id,
LEAST(IFNULL(NULLIF(saleprice, 0), price),price) AS sort_price,
GROUP_CONCAT(CONVERT(category_id, CHAR(8))) AS cats
FROM
(`gc_category_products`)
JOIN `gc_products` ON `gc_category_products`.`product_id` = `gc_products`.`id`
WHERE
`category_id` = '31'
OR `category_id` = '35'
OR `category_id` = '30'
OR `category_id` = '36'
OR `category_id` = '37'
OR `category_id` = '34'
AND
`enabled` = 1
GROUP BY
`product_id`
LIMIT 10
And in the cats
field, it only returns one number, if I take out all the WHERE clauses, it brings back a comma delimited list, but I need those WHEREs to filter my data.
Any ideas?
Sample data
gc_category_products
--------------------------
product_id | category_id |
--------------------------
3 | 31 |
3 | 18 |
4 | 35 |
4 | 21 |
gc_products
-----------------------------------
| id | price | saleprice | enabled|
-----------------------------------
| 3 | 23.00 | 0.00 | 1 |
| 4 | 50.00 | 0.00 | 1 |
expected result
--------------------------------
product_id | sort_price | cats |
--------------------------------
| 3 | 23.00 | 31,18|
| 4 | 50.00 | 35,21|
Upvotes: 1
Views: 117
Reputation: 169544
The WHERE
clause filters out the additional category_id
s you expect in your resultset: '18'
and '21'
. This query produces the resultset you desire:
SELECT
gc_category_products.product_id,
LEAST(IFNULL(NULLIF(saleprice, 0), price),price) AS sort_price,
GROUP_CONCAT(CONVERT(category_id, CHAR(8))) AS cats
FROM
(`gc_category_products`)
JOIN `gc_products` ON `gc_category_products`.`product_id` = `gc_products`.`id`
WHERE
`category_id` = '31'
OR `category_id` = '35'
OR `category_id` = '30'
OR `category_id` = '36'
OR `category_id` = '37'
OR `category_id` = '34'
OR `category_id` = '18' /* added */
OR `category_id` = '21' /* added */
AND
`enabled` = 1
GROUP BY
`product_id`
LIMIT 10
A tested example producing the resultset you expect can be found here: http://sqlfiddle.com/#!2/71a20/17
However, please note that there are other potential issues with your query.
OR
logical operator has lower precedence than AND
. So your query may not do what you expect. If Joachim has correctly interpreted your intent you should modify your WHERE
clause to include a subquery which performs the filtering. Structuring the query in this way ensures that the additional categories are not filtered from your final resultset.
WHERE
enabled
= 1
AND EXISTS (
SELECT 1
FROM gc_category_products
as x
WHERE x
.product_id
= gc_products
.id
AND x
.category_id
IN
('31','35','30','36','37','34')
)
This additional modification is tested to be working to your specifications here: http://sqlfiddle.com/#!2/71a20/21/0
Upvotes: 3
Reputation: 181097
To me this sounds like what you want, you want the products that are in certain categories, including a list of all categories they're in;
SELECT
cat.product_id,
LEAST(IFNULL(NULLIF(saleprice, 0), price),price) AS sort_price,
GROUP_CONCAT(category_id) AS cats
FROM gc_category_products cat
JOIN gc_products p ON cat.product_id = p.id
WHERE cat.product_id IN
(SELECT product_id
FROM gc_category_products gcp
WHERE gcp.category_id IN ('31','35','30','36','37','34'))
AND enabled=1
GROUP BY product_id
PRODUCT_ID SORT_PRICE CATS
3 23 31,18
4 50 35,21
Upvotes: 5