1

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

0

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

  • yes this is working fine thank you so much, but if i wnt to use where clause to the same query then? i mean to get data for perticular collage – Schüler May 6 '18 at 13:28
  • Please, add it to your question. – McNets May 6 '18 at 13:29
  • YEs added please check;) – Schüler May 6 '18 at 13:32
  • i tried this select std_id from tbl where collage_id = 2 group by std_id having count(distinct collage_id) = 1but it is returning all data i mean 2,6,7 & 8 ;( – Schüler May 6 '18 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.