Selecting single row based on a condition

Question

Consider the following table structure

Table Structure

 Table "public.studenttable"
 Column  |  Type   | Modifiers 
---------+---------+-----------
 id      | integer | not null
 uid     | text    | not null
 subject | text    | 
Indexes:
    "studenttable_pkey" PRIMARY KEY, btree (id, uid)

select * from studenttable

 id | uid | subject 
----+-----+---------
  1 | S01 | Maths
  1 | S02 | Science
  1 | S03 | English
  2 | S02 | English

My objective is to get rows which have only English as a subject which should return id = 2 row only. I have following approaches in mind

1) Return subjects as a string separated by semicolon for particular id and run a loop to match violations.

select id,(array_agg(subject),';') 
from studenttable 
group by id

2) The second approach is to do a self join on studenttable to make sure that id = 1 has only one subject.

I am not able to form the final query. Also is there any alternative?

Answer 1

SELECT id, uid
FROM studenttable
GROUP BY id, uid
HAVING SUM( CASE WHEN subject='English' 
                 THEN 0 
                 ELSE 1 
            END ) = 0

Answer 2

Assuming that the same id cannot have the same subject more than once.

SELECT *
FROM   studenttable s
WHERE  subject = 'English' 
AND    NOT EXISTS (
   SELECT FROM studenttable s1
   WHERE s1.id = s.id
   AND   s1.uid <> s.uid  -- exclude self
   );

Find all rows with subject='English' where no other row for the same id exists.

The query is fast as it can use the index backing your PK. We could also use

AND   s1.subject <> 'English'

Or

AND   s1.subject <> s.subject

To allow duplicate entries for 'English', but that would be slower without index support.

Related:

• How do I remove duplicate records in a join table in PostgreSQL?