SQL select to eliminate duplicate value that has 2 other values in next column

Question

I have constructed a junction table which goes like this.

Table Name: myTable

p_id | c_id
-----------
1     1
1     2
1     3
2     2    
2     3
3     2
3     3
3     4

I wanted to SELECT p_id that doesn't have both c_id 3 and 4. In this case only p_id 3 has both c_id 3 and 4 so after the select statement the query should return both p_id 1 and 2. The thing is that I try different kind of method but still it wouldn't work. I really need help.

my query

1.) SELECT DISTINCT p_id FROM myTable WHERE c_id != 3 AND course_id != 4;

Problem: It still returns 3 as one of the result since 3 has c_id of 2

Answer 1

Something like this:

SELECT DISTINCT p_id 
FROM   mytable 
WHERE  p_id NOT IN (SELECT p_id 
                    FROM   mytable 
                    WHERE  c_id IN ( 3, 4 ) 
                    GROUP  BY p_id 
                    HAVING Count(DISTINCT c_id) = 2)

SQLFiddle demo

Answer 2

Try this:

SELECT DISTINCT p_id 
FROM myTable 
WHERE c_id IN (3,4) 
GROUP BY p_id HAVING COUNT(DISTINCT c_id)<2

Answer 3

The straightforward solution is to use exists:

  select 
distinct p_Id
    from myTable t
   where not (exists (select 1
                        from myTable
                       where (c_id = 3) and
                             (p_id = t.p_id)) and 
              exists (select 1
                        from myTable
                       where (c_id = 4) and
                             (p_id = t.p_id)))

Answer 4

Try this:

SELECT mytable.p_id 
FROM   mytable 
       LEFT OUTER JOIN (SELECT v1.p_id 
                        FROM   (SELECT p_id 
                                FROM   mytable 
                                WHERE  c_id = 3) v1 
                                INNER JOIN (SELECT p_id 
                                            FROM   mytable 
                                           WHERE  c_id = 4) v2 
                                       ON v1.p_id = v2.p_id) v 
                    ON mytable.p_id = v.p_id 
WHERE  v.p_id IS NULL 
GROUP  BY mytable.p_id 

Answer 5

Try this:

select distinct mytable.p_id from mytable where c_id not in (3,4) and p_id <>3

This will give result which does not have 3 and 4