Check if fields exist in other table

I have two queries, one is to check the no. of times the STUDENTNO exists in the table Subjects

SELECT COUNT(*) AS COUNT 
FROM Subjects R  
INNER JOIN students w W ON R.studentno = W.studentno 
WHERE R.studentno = '89514'

Next is to get the valid students (whose name and student no doesn't exist in the table SUBJECT):

SELECT DISTINCT W. * 
FROM STUDENTS W 
LEFT JOIN SUBJECTS R ON W.STUDENTNO + w.NAME = R.STUDENTNO + r.NAME   开发者_运维技巧
WHERE R.STUDENTNO + r.NAME IS NULL

I didn't get any output here. I still need to get those whose STUDENTNO exist in the SUBJECT table, but I guess this doesn't retrun it. Help. Please. Thanks

Couple suggestions for your second query:

• use meaningful table aliases! Why is Students aliased to be W ??
• if you want to join on multiple columns - do so separate, not by concatenating together two columns... also: is StudentNo a primary key?? If so: checking for the primary key to match would be sufficient - no need to add an extra condition that doesn't add any value to the JOIN.....

Try this:

SELECT DISTINCT stu.* 
FROM Students stu
LEFT JOIN Subjects sub ON stu.StudentNo = sub.StudentNo AND stu.Name = sub.Name  
WHERE sub.StudentNo IS NULL

or if StudentNo is the primary key, then maybe this will do:

SELECT DISTINCT stu.* 
FROM Students stu
LEFT JOIN Subjects sub ON stu.StudentNo = sub.StudentNo 
WHERE sub.StudentNo IS NULL

Does that return anything??

The first query can be simplified to:

SELECT COUNT(*)
  FROM Subjects
 WHERE StudentNo = '89514';

The second can probably be simplified to:

SELECT *
  FROM Students
 WHERE StudentNo NOT IN (SELECT StudentNo FROM Subjects);

This formulation does assume that the names in the Subjects table match the names in the Students table. The design of the database is (seriously) flawed if the student name is recorded in the Subjects table too. For instance, you can't update the name in the Students table without also updating the matching rows in the Subjects table - which is bad.