CODEWITHSUNDEEP
mysqlsql
adamzwakk
adamzwakk

Reputation: 709

Trying to get comma delimited list but only get one

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

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169544

The WHERE clause filters out the additional category_ids 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.

  1. The OR logical operator has lower precedence than AND. So your query may not do what you expect.

  2. 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

Joachim Isaksson
Joachim Isaksson

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

SQLfiddle for testing.

Upvotes: 5

Related Questions