I have 2 tables - 1. table "products" with ID and STATUS columns (ex. status = int 1;2;3...) and 2. table "product_codes" with ID, PRODUCT_ID and CODE columns. CODES aren't UNIQUE and they could repeat in different rows. How to make SQL Query for every unique CODE COUNT where product STATUS is ex. 1; 3; 5;

Products table example:

|ID|STATUS|
-----------
|1 | 2    |
|2 | 3    |
|3 | 1    |
|4 | 5    |
|5 | 3    |
|6 | 5    |
|7 | 3    |

Product_codes table example:

|ID|Product_ID|CODE |
---------------------
|1 | 2        |code1
|2 | 3        |code6
|3 | 1        |code2
|4 | 5        |code2
|5 | 3        |code2
|6 | 5        |code4
|7 | 7        |code1

Needed output would be: Count of codes where product status is 3 and 5

|CODE |COUNT|
------------
|code1|  2 |
|code2|  1  |
|code4| 1 |
... and so on...

I am using HeidiSql

CodePudding user response：

Return a count group by code for all occurrences of 3 OR 5 (not 3 AND 5)

SELECT PC.Code, Count(P.Status) as `Count`
FROM Product_Codes PC
INNER JOIN Products P
 on P.ID = PC.Product_ID
WHERE P.Status in (3,5)
GROUP BY CODE

You do not indicate how you want to handle the situation when the status is not in the defined list. Say you had a product code Code10 with a status of only 1. Do you want to see 0 or not the code10 at all? Do you want to see a 0 count or do you want to not see the product Code at all?

The SQL above assumes you only want to see a record if there is an associated "product status" If you did want to see it All of them, we would need to switch our inner to a left join and possibly coalesce the count and move the where clause to the on clause of the join.

your edge cases are not well defined. which is why people are asking for better expected results and sample data.

• the do something step
• (next) laugh
• (followed by) Do it right
• (and finally given time) make it better