sql query help

Question

hi

I have a database with some columns
The first column contains the student's name
The second column contains the student's assessment or rate
The third column contains the test number for students

Student evaluation is as follows:
My number is 0-10
Or my string : # - not

A query is required to fill out the datatable and bite into the DataGrid View, where the sql database is as follows:

Note: The same students take exams in the form of a first ' t1', second' t2', third, etc.
Show the required students in DataGrid view, where the following is:
Every student has his data.
Each student is adjusted according to the test number. If a student takes an assessment, the student's grade appears. If nothing is taken, the student's name below the test number skips the student.

If a student takes a 'not' text assessment, on one test number and another 'not' test number, then the student skips each test for the same assessment, as the number of tests is not specified.
If a student takes a 'not' assessment and another test with the same student's name, Nothing, the student appears on the tests

An image of the database

result

my query not good result

SELECT STU_TB.[STU_ID], STU_TB.[STU_NAME], STU_TB.[STU_RATE], STU_TB.[STU_TEST] FROM STU_TB where stu_rate <>null and stu_name not in (select stu_name from stu_tb where stu_rate='NOT' or stu_rate=null group by stu_name HAVING COUNT(stu_name) > 1) order by stu_test;  

Sql .bak
view

Answer 1

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.

Answer 2

Adding to what Karen says, but from an SQL perspective: for this type of question, we recommend that you post CREATE TABLE statements for your tables and INSERT statements with sample data and the desired result given the sample. By providing everything in SQL format, it is easy to copy and paste into a query window to develop a tested solution.

However, I do spot some errors in your query:

SELECT STU_TB.[STU_ID], STU_TB.[STU_NAME], STU_TB.[STU_RATE], STU_TB.[STU_TEST] 
FROM STU_TB 
where **stu_rate <>null** 
    and stu_name not in (select stu_name from stu_tb 
                      where stu_rate='NOT' or **stu_rate=null** 
                      group by stu_name 
                      HAVING COUNT(stu_name) > 1) 
order by stu_test;

As I start, I have reformatted the query to make it legible. I have also highlighted to comparisons with NULL that are unlikely to be correct. NULL represents an unknown value, and comparisons with NULL do not yield TRUE or FALSE, but they yield UNKNOWN. When you say stu_rate <> NULL, that NULL value could by chance be equal to the value of stu_rate. So we don't know for sure. And if stu_rate is NULL, well, it could be the same unknown value as the other NULL, or they could be different; we don't know.

The correct way is to the IS [NOT] NULL operator, so rewrite you query with this operator and maybe you get the desired result.

(Yes, you attached a database backup. That seems an overkill for this problem. And I did not read your problem description very clearly. I just spotted the incorrect use of NULL.)