MYSQL how to check if there are multiple rows other than value x-CodePudding

I have a mapping table that maps certain ID's to eachother (products and categories). Now I need to check whether or not in this mapping category there are multiple rows. For example every product has a standard mapping of category id of 1. Now there could be more rows that have other category id's. For example:

Product_id  Category_id
1           1
1           2
2           1
3           1 
4           1
4           2

I need to only select those rows that only have the product id and the category id of 1. So in this case I want to selct product with id 2,3. Because 1 and 4 have multiple category id's.

I have this query (it's a join because I want to add some other data in this query):

SELECT * FROM `products` as P
LEFT JOIN `product_categories` as PC
ON PC.`product_id` = P.`product_id`
WHERE PC.`category_id` = 1 AND 
LIMIT 10

Now I don't know exacly what I need to do in the Where statement. Can anyone help me?

I thought about using count, but I don't think that is the best solution. Maybe check if there are other values other than 1 in the category id or something?

CodePudding user response：

I have considered your table as a reference. You can add or perform join after that.

You can not add where here. The count is a group function (also called aggregate function). So you have to add having instead of where.

Creating the table:

CREATE TABLE IF NOT EXISTS `test` (
  `product_id` int(6) unsigned NOT NULL,
  `category_id` int(6) unsigned NOT NULL
) DEFAULT CHARSET=utf8;
INSERT INTO `test` (`product_id`, `category_id`) VALUES
(1,1),
(1,2),
(2,1),
(3,1), 
(4,1),
(4,2)

Result generation SQL:

select product_id,category_id 
from test group by product_id having count(product_id)<2

Output:

product_id  category_id
----------  ------------
     2           1
     3           1