CODEWITHSUNDEEP
mysqlsql
pankaj
pankaj

Reputation: 8348

SQL to check if the value is not present in table

I am working on following SQL: 

select *
from `STUDENTLOC` l,
    STUDENT s,
    ATTENDANCE a
where l.STUDENTID = s.ID
    and l.LOCID = 3

Now I need to make sure that the values are not already present in ATTENDANCE table. It has following structure:

ID  StudentID   ScheduleID
1      6            6   
2      3            3

It is a simple list where I need to display list of students whose record have not been added in ATTENDANCE table.

Upvotes: 0

Views: 2894

Answers (2)

b2ok
b2ok

Reputation: 546

Example table student and table payments:

id_student  name        id  id_student datepayment
1         Lisa         1    1        2017-01-01
2                      2    1        2017-02-03
3         Asher        3    2        2017-03-05
4         Lee          4    1        2017-03-03


SELECT a.name, a.datepayment
    FROM
    (SELECT s.name, p.datepayment
        FROM
        students s
        LEFT OUTER JOIN payment p ON s.id_student = p.id_student)  AS a       
    WHERE datepayment IS NULL;

Result:

   name    datepayment
    Asher       NULL
    Lee         NULL

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use not exists:

select *
from `STUDENTLOC` l
join STUDENT s on l.STUDENTID = s.ID
where not exists (
        select 1
        from ATTENDANCE a
        where a.STUDENTID = l.STUDENTID
        )
    and l.LOCID = 3

Also, always use modern explicit join syntax instead of comma based join syntax.

Upvotes: 2

Related Questions