question

AMERSAID-7084 avatar image
0 Votes"
AMERSAID-7084 asked ErlandSommarskog commented

sql query help

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

91814-msdna.png

91866-stud1.png


result
92171-msdnb.png


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


sql-server-transact-sqldotnet-sqlclient
msdna.png (21.4 KiB)
stud1.png (28.0 KiB)
msdnb.png (16.2 KiB)
msdnb.png (16.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello, usually the best way to write your SQL for SQL-Server is not in code but instead use SSMS (SQL-Server Management Studio). Write your SQL with the assistance of first performing a normal SELECT, highlight the query, select the designer, rough out the query. Next, declare parameters and set values to see if you get the proper results.

Note we don't have your database so it makes it impossible to give an exact solution and even so you need to learn how to first validate the model will provide what you want then go from there, adjust as needed.

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered MelissaMa-msft edited

Hi @AMERSAID-7084,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.)

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HI

ErlandSommarskog

How do I improve myself with references or sources to learn queries

0 Votes 0 ·

It can be a good idea to pick up a book. Here is one suggestion:
https://www.amazon.com/T-SQL-Fundamentals-3rd-Itzik-Ben-Gan/dp/150930200X/ref=sr_1_2?dchild=1&keywords=learning+T-SQL&qid=1619731448&sr=8-2

I should that Itzik is a good friend of mine. But he is also known as one of the best SQL teachers on the planet.

0 Votes 0 ·