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

First I must highlighten here the condition you are using in query is for course_id field that is not in table. — Nitu Dhaka, Dec 31 '13 at 07:40

score 2 · Answer 1 · edited Dec 31 '13 at 09:32

2

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

edited Dec 31 '13 at 09:32

Hawk

5,060
12
49
74

answered Dec 31 '13 at 07:33

valex

23,966
7
43
60

score 0 · Answer 2 · answered Dec 31 '13 at 07:35

0

Try this:

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

answered Dec 31 '13 at 07:35

Saharsh Shah

28,687
8
48
83

This query doesn't return `p_id` which has no 3 or 4 at all. – valex Dec 31 '13 at 07:38

score 0 · Answer 3 · answered Dec 31 '13 at 07:36

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)))

score 0 · Answer 4 · edited Dec 31 '13 at 09:33

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

Radhamani Muthusamy · Answer 5 · 2013-12-31T10:34:29.513

0

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

edited Dec 31 '13 at 10:34

answered Dec 31 '13 at 09:46

Radhamani Muthusamy

328
4
13

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

my query

5 Answers5