Hi @AMER SAID ,
Welcome to Microsoft Q&A!
For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.
Please refer below and check whether it is working.
create table STU_TB
(STU_ID INT,
STU_NAME VARCHAR(20),
STU_RATE CHAR(4),
STU_TEST CHAR(2))
INSERT INTO STU_TB VALUES
(1,'ALIE','0','T1'),
(2,'SAIED',NULL,'T1'),
(3,'EBRAHIM','#','T1'),
(4,'SALIM','NOT','T1'),
(5,'MAHER',NULL,'T1'),
(1,'ALIE','0','T2'),
(2,'SAIED','NOT','T2'),
(3,'EBRAHIM','1','T2'),
(4,'SALIM','NOT','T2'),
(5,'MAHER','2','T2')
SELECT [STU_ID], [STU_NAME], [STU_RATE], [STU_TEST]
FROM STU_TB
where ([STU_RATE] is not null and [STU_RATE]<>'NOT')
or (stu_name in (select stu_name from stu_tb
where stu_rate='NOT'
group by stu_name
HAVING COUNT(stu_name) =1) )
order by stu_test;
Output:
STU_ID STU_NAME STU_RATE STU_TEST
1 ALIE 0 T1
2 SAIED NULL T1
3 EBRAHIM # T1
1 ALIE 0 T2
2 SAIED NOT T2
3 EBRAHIM 1 T2
5 MAHER 2 T2
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.