Counting with comma separated IDs 1

[Sleidia]

Hi

I would like to know if the following can be done with one single query.

Let's say we have :

table_categories with columns cat_ID, cat_name
table_products with columns prod_ID, prod_cat_ID, prod_name

prod_cat_ID contains the category IDs for each product.
It can contain a single ID, BUT, it can contain mutliple IDs too ( commas separated in that case ).

Now, if I want to loop through all the categories and count the number of products found in every category like this :

T-shirts : 20 products
Sweats : 33 products
Hats : 22 products
....

How should I do with one query?

Thanks for the help!

 

[rac2]

No. You will need to write a procedure to parse and count the comma-delimited values.

If possible you should re-design your database to have a new table which has one row for each prod_cat_ID value. This table would have two columns, one for prod_ID and one for prod_cat_ID. A product which fits into three categories woul have three rows in the new table. This will provide a structure in which SQL can be used to count the number of products in a category.

 

[r937]

procedure not necessary

however, i strongly support rac2's recommendation to normalize the data to eliminate the comma-delimited string of prod_cat_ids

the following query works, but will not scale (i.e. it gets slower as the tables get larger, because it requires a table scan)

SELECT table_categories.cat_name
     , COUNT(*) AS products
  FROM table_categories 
INNER 
  JOIN table_products 
    ON CONCAT(',' , table_products.prod_cat_ID , ',') 
  LIKE CONCAT('%,' , table_categories.cat_ID , ',%')
GROUP 
    BY table_categories.cat_name

notice that you must concatenate commas to the front and back of both the searched comma-delimited string, as well as the cat_id being searched (inside the wildcards), so that the LIKE operator finds the correct id numbers

r937.com | rudy.ca

 

[Sleidia]

Thanks a lot r937 !
You always are my savior

I knew of the CONCAT trick but it was the JOIN statement that I had difficulties with. Frankly, I did read the documentation about JOIN statements several times and I always failed to grasp the logic behind. I'll need to find a tutorial written differently I suppose

For example, in your query, I don't see where is the link between "COUNT(*) AS products" and the JOIN statement because "products" is stated nowhere else !

As for the normalization, I know you're totallly right but I find it a hassle to have one piece of information scattered among different tables. I will have to run a complex query everytime I want to display the relations between products and categories.

With the "dirty" method, would hundreds of products suffice to slow down the system? Or were you talking about a much larger database?

I know that scalability is very important but, there are cases when a system is designed for a specificly sized purpose, right?

 

[r937]

i'm sure you will understand sql better, the more you work with it

as for the normalization, i would not use the word "scattered" to describe it

your queries will continue to be extremely difficult to write (and slow to execute) as long as you use comma-delimited lists of ids in a single column

good luck



r937.com | rudy.ca

 

[Sleidia]

Sorry but there is one thing I don't understand with the query you gave : How do you also display the empty categories in the result?

I've tried with LEFT JOIN, but then, the number of products systematically becomes "1" instead of "0" for all the categories that are empty. I don't get it

 

[r937]

change it like this:

SELECT table_categories.cat_name
     , COUNT([red]table_products.prod_cat[/red]) AS products
  FROM table_categories 
[red]LEFT OUTER[/red]
  JOIN table_products 
    ON CONCAT(',' , table_products.prod_cat_ID , ',') 
  LIKE CONCAT('%,' , table_categories.cat_ID , ',%')
GROUP 
    BY table_categories.cat_name

r937.com | rudy.ca

 

[Sleidia]

I'll try that .... thanks!

Counting only in the right column was the obvious thing that I didn't think about As for the LEFT OUTER thingie, it's beyond my understanding.

Well, what I need right now is a good sleep

Thanks again