I need help to find solution for this query in MySQL?

Question

I have MySQL table as follow:

id  p_id   c_id

1   11      1

2   11      2

3   11      3

4   12      1

5   12      3

6   13      1

7   13      2

I need a query that when c_id is 1 and 2, it should return 11 and 13 of p_id.

I have tried the following query:

SELECT DISTINCT p_id FROM `Table Name` where c_id in (1,2)

which returns: 11, 12, 13.

but I only need it to return: 11 , 13.

Answer 1

You can write your query as:

SELECT DISTINCT a.p_id 
FROM table_name AS a
JOIN table_name AS b ON a.p_id=b.p_id
WHERE a.c_id ='1' AND b.c_id ='2';

It's a self-join on the table itself

Answer 2

SELECT DISTINCT a.p_id 
FROM table_name AS a
JOIN table_name AS b ON a.p_id=b.p_id
WHERE a.c_id ='1' AND b.c_id ='2';

it worked fo me