Mysql查询其他查询的结果集

Question

I have 2 custom tables in my MySQL database (DB name: test).

The data of my tables is given below:

Table: sku

entity_id   sku
1           24-MB01
2           24-MB03
3           24-UB02
4           24-UB01

Table: cat

id  entity_id   category
1   1           3
2   2           3
3   1           5
4   2           7
5   4           4
6   3           50
7   3           20
8   3           21
9   4           3

I am using the following query to get the comma separated categories with respect to sku values:

SELECT sku.entity_id, sku.sku, GROUP_CONCAT(cat.category SEPARATOR ",") as category 
FROM sku, cat 
WHERE cat.entity_id = sku.entity_id 
GROUP by sku.entity_id

Below is the result set found using the above query:

entity_id   sku         category
1           24-MB01     3,5
2           24-MB03     7,3
3           24-UB02     21,50,20
4           24-UB01     4,3

Now, I want to use this result set to find records for a particular category (say the category id is 3). When I used FIND_IN_SET in my query, I am getting only 3 in the category column in the result. I want to get it like:

entity_id   sku         category
1           24-MB01     3,5
2           24-MB03     7,3
4           24-UB01     4,3

How can I achieve the desired result?

Answer 1

Add a HAVING clause:

SELECT sku.entity_id, 
    sku.sku, 
    GROUP_CONCAT(cat.category SEPARATOR ",") as category 
FROM sku, cat 
WHERE cat.entity_id = sku.entity_id 
GROUP by sku.entity_id
HAVING SUM(cat.category = 3) > 0;

(This makes use of true = 1, false = 0 in MySQL, by the way. In other DBMS that would be HAVING SUM(CASE WHEN cat.category = 3 THEN 1 ELSE 0 END) > 0.)