Fetch rows based on their values in a group in SQL Server table

Question

id      dept         Person       Rating
-------------------------------------------
1       ece            p1           R1  
2       ece            p2           t1
3       eee            P3           R2             
4       eee            p4           M
5       Civil          P5           R2
6       Civil          P6           t2
7       Civil          P7           t2
8       Mech           p8           R2
9       Mech           P9           Null
10      IT             P10          R2J
11      IT             P11          T2
12      IT             P12          T2

I would like to fetch all the dept rows whose ratings are null in a dept ,or whose ratings are M in a dept, or whose " t" ratings are different to that of "P" in a dept.

Desired output:

3       eee            P3           R2             
4       eee            p4           M
8       Mech           p8           R2
9       Mech           P9           Null
10      IT             P10          R2J
11      IT             P11          T2
12      IT             P12          T2

Note 1: "t" ratings are different to that of "R" in a dept, it means if a person rating in a dept is R2 then other persons in dept ratings is T1. Example dept IT.

Note 2: "t" ratings are same to that of "R" in a dept, it means if a person rating in a dept is R1 then other persons in dept ratings is T1. Example dept ECE.

Thank you in advance.

Answer 1

(from another question of yours I doubt you are understanding the replies, anyway I hope you at least try)

Select * from myTable t1
where exists (
select * from myTable t2 
where t1.dept = t2.dept AND
  (t2.rating = 'M' or 
   t2.rating is null or 
   right(t1.Rating,1) <> right(t2.Rating,1))
);

DBFiddle Demo