0

I have this query

SELECT 
   products_list.id_cat_unique,
   products_categories_list.*,
   COUNT(products_list.id_cat_unique) as counter
FROM products_categories_ids
LEFT JOIN products_list 
   ON products_categories_ids.id_cat_unique = products_list.id_cat_unique
LEFT JOIN products_categories_list 
   ON products_categories_list.id_cat_unique = products_categories_ids.id_cat_unique 
GROUP BY products_categories_list.name_cat
ORDER BY products_categories_list.name_cat ASC

This is complicated to explain without having the tables on your screen but I'll try
what I want is count all the rows from products_list that can be associated with max two rows per id_cat_unique (contained in products_categories_list) that are English or Italian. This two items each could have infinite id_products in products_list of course. I want the result to be grouped by products_categories_list.name_cat and by the language. What I get with this query is categories grouped by name/unique id which is correct, the problem is that the counter value for both English and Italian turns out as the sum of all the products associated with one id_cat_unique regardless of the language. So if I have 12 rows from one category in English and 3 for the same category in Spanish I get 15 as counter for both the languages.

EDIT ADDING TABLES STRUCTURES

products_list

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id_product        | int(11)      | NO   | PRI | NULL    | auto_increment |
| id_product_unique | int(5)       | NO   |     | NULL    |                |
| lang              | varchar(2)   | NO   |     | NULL    |                |
| name_product      | varchar(200) | NO   |     | NULL    |                |
| desc_product      | text         | NO   |     | NULL    |                |
| id_cat_unique     | int(2)       | NO   |     | NULL    |                |
| status            | int(1)       | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

products_categories_ids

+---------------+--------+------+-----+---------+----------------+
| Field         | Type   | Null | Key | Default | Extra          |
+---------------+--------+------+-----+---------+----------------+
| id_cat_unique | int(5) | NO   | PRI | NULL    | auto_increment |
+---------------+--------+------+-----+---------+----------------+
1 row in set (0.00 sec)

products_categories_list

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id_cat        | int(5)       | NO   | PRI | NULL    | auto_increment |
| id_cat_unique | int(2)       | NO   |     | NULL    |                |
| lang          | varchar(2)   | NO   |     | NULL    |                |
| name_cat      | varchar(500) | NO   |     | NULL    |                |
| date_created  | int(11)      | NO   |     | NULL    |                |
| date_modified | int(11)      | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

result from query

| id_cat_unique | id_cat | id_cat_unique | lang | name_cat                       | date_created | date_modified | counter |
+---------------+--------+---------------+------+--------------------------------+--------------+---------------+---------+
|             1 |     18 |             1 | it   | Carne di suino                 |   1308267538 |    1308267538 |       6 |
|            14 |     21 |            14 | it   | Guanciali                      |   1308777322 |    1308777322 |       2 |
|             3 |     20 |             3 | it   | Pollo a pezzi                  |   1308267892 |    1308267892 |       2 |
|             1 |     22 |             1 | en   | Pork meat                      |   1308267538 |    1312383232 |       6 |
|            14 |     23 |            14 | en   | Sheeps                         |   1308777322 |    1312383220 |       2 |
|             2 |     19 |             2 | it   | That's a "test" |   1308267538 |    1308267538 |       7 |
+---------------+--------+---------------+------+--------------------------------+--------------+---------------+---------+
6 rows in set (0.00 sec)

"Pork meat" and "Carne di Suino" have respectively 1 and 5 products (from the same category but different language, i wanna group the counting by language too) instead it shows 6 for both

NEW question

I'd like to use this query for many purposes without using different things, so accordingly if I want to retrieve the categories with ONE language I'd add one where clause

select pcl.*,
   (select count(*) from products_list pl 
       where pcl.id_cat_unique = pl.id_cat_unique 
         and pcl.lang = pl.lang) as counter
from products_categories_list pcl
where products_list.lang = 'en' <- added
ORDER BY pcl.name_cat ASC

I get Unknown column 'products_list.lang' in 'where clause'. Why?

Improve this question

2 Answers 2

Reset to default
1

I THINK this will work, if not, it's going to be close, let me know what you get, and I can modify it. I've modified the query around. Would this work?

select pcl.*,
   (select count(*) from products_list pl 
       where pcl.id_cat_unique = pl.id_cat_unique 
         and pcl.lang = pl.lang) as counter
from products_categories_list pcl
ORDER BY pcl.name_cat ASC

The PCL and PL are just aliases for the tables, so I didn't have to always write out products_categories_list and products_list. You specify the alias in the from part of the statement, so from products_list pl allows you to refer to products_list as pl in the rest of the query.

The idea behind this is that you have a unique row for each products_categories_list and you want a count of a secondary table. This could have been done by group by or by a sub query. I put the correlated subquery in to count the number of rows in products_list that matched the products_categories_list and language, since you are getting all the columns out of the products_categories_list. The correlated subquery allows you to get a single value (count(*)) from a nested query.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I already used something like this...I tried and I get the same result as mine :(
it works!! finally! thank you! Now, you're gonna have to explain :) are those acronyms? I didn't even know it was possible to do it! What do those pl and pcl after the tables names mean?
The parent query doesn't know about product_list, and once you've added the alias, you can only use that name. So the where clause should be where pcl.lang = 'en'
thank you, i think I'll never understand how to read the queries, from right to left? WHERE and ORDER BY included?
0

Probably this: add DISTINCT

COUNT(DISTINCT products_list.id_cat_unique) as counter

This will ignore duplicate, so the 12+3 via the JOIN becomes 1+1

Improve this answer

1 Comment

sorry it's not what I want but it's my fault, its impossible to understand like this, how can I show the structure of table?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.