CODEWITHSUNDEEP
c#sqlsql-server
RedRocket
RedRocket

Reputation: 1733

How to filter null value in sql

I have a situation in my sql statement. In this sql statement, I joined 3 tables together (Application_Detail, Teacher_Detail, and Class_Detail), and than I used WHERE to filter my table to find out how many teacher used this application, I found the following result.

As you can see in the 1st record, both teacher related and class related field are null. I am wondering is there a way to filter out the 1st record and only show the 2,3,4 record? because I want to only want to show the record if there are some value in teacherId, teacherName, class, or grade column. 

   teacherId teacherName   applicationName  class   grade
1. NULL      NULL         Excel            NULL     NULL
2. 5         NULL         Excel            NULL     NULL
3. NULL      NULL         Excel            A        6
4  NULL      NULL         Excel            B        2

Here is my SQL command

SELECT
   td.teacherId,
   teacherName,
   applicationName,
   class,
   grade
FROM
   [AppUser_Detail] as aud
LEFT OUTER JOIN [Teacher_Detail] as td ON aud.teacherId = td.teacherId
LEFT OUTER JOIN [Application_Detail] as ad ON aud.applicationId = ad.applicationId
LEFT OUTER JOIN [Class_Detail] as cd ON aud.classId = cd.classId
WHERE
aud.applicationId = 6 //I filter if my application Id is 6

Upvotes: 4

Views: 3400

Answers (3)

thejustv
thejustv

Reputation: 2035

SELECT *
FROM (
    SELECT td.teacherId AS [TeacherID]
        ,teacherName AS [TeacherName]
        ,applicationName AS [ApplicationName]
        ,class AS [Class]
        ,grade AS [Grade]
    FROM [AppUser_Detail] AS aud
    LEFT JOIN [Teacher_Detail] AS td ON aud.teacherId = td.teacherId
    LEFT JOIN [Application_Detail] AS ad ON aud.applicationId = ad.applicationId
    LEFT JOIN [Class_Detail] AS cd ON aud.classId = cd.classId
    WHERE aud.applicationId = 6
    )
WHERE TeacherID IS NOT NULL
    OR TeacherName IS NOT NULL
    OR Grade IS NOT NULL

Upvotes: 0

Dzmitry Paliakou
Dzmitry Paliakou

Reputation: 1627

SELECT
   td.teacherId,
   teacherName,
   applicationName,
   class,
   grade
FROM [AppUser_Detail] as aud
LEFT OUTER JOIN [Teacher_Detail] as td ON aud.teacherId = td.teacherId
LEFT OUTER JOIN [Application_Detail] as ad ON aud.applicationId = ad.applicationId
LEFT OUTER JOIN [Class_Detail] as cd ON aud.classId = cd.classId
WHERE
aud.applicationId = 6 //I filter if my application Id is 6 
AND NOT (td.teacherId IS NULL AND class IS NULL AND grade IS NULL)

Upvotes: 2

Yael
Yael

Reputation: 1600

Try this:

SELECT
   td.teacherId,
   teacherName,
   applicationName,
   class,
   grade
FROM
   [AppUser_Detail] as aud
LEFT OUTER JOIN [Teacher_Detail] as td ON aud.teacherId = td.teacherId
LEFT OUTER JOIN [Application_Detail] as ad ON aud.applicationId = ad.applicationId
LEFT OUTER JOIN [Class_Detail] as cd ON aud.classId = cd.classId
WHERE
td.teacherId is not null OR class is not null OR grade is not null

Upvotes: 5

Related Questions