# Postgresql – How to fetch the records which is belongs to only one group in postgresql

postgresqlpostgresql-9.3

I want to fetch the student_id which is only belongs to one collage
expected op
6 and 7 stud_id's so how can i do this?

 _id | stud_id | collage_id | university_id  | active
-----+---------+------------+----------------+--------
1 |       2 |          2 |              2 | t
6 |       2 |          3 |              3 | t
7 |       6 |          2 |              2 | t
8 |       7 |          2 |              2 | t
9 |       8 |          2 |              2 | t
10 |       8 |          3 |              2 | t


and also if i need fetch the data which is belongs to perticular collage , i mean to use where clause ? thank you

You could use count(distinct collage_id) grouping by stud_id in this way:

select   stud_id
from     tbl
group by stud_id
having   count(distinct collage_id) = 1;

| stud_id |
| ------: |
|       6 |
|       7 |


db<>fiddle here

If you want to fetch rows filtering by collage_id:

select *
from   tbl
where  collage_id = 3
and    stud_id in (select   stud_id
from     tbl
group by stud_id
having   count(distinct collage_id) = 1);


Note I've modified a bit your sample data.

_id | stud_id | collage_id | university_id | active
--: | ------: | ---------: | ------------: | :-----
8 |       7 |          3 |             2 | t


db<>fiddle here