CODEWITHSUNDEEP
sqlms-access
Renier Swart
Renier Swart

Reputation: 85

Logic of a SQL Query

My Query does not work logically. I am trying to get a list of students who studied any AutoCAD course who has not studied any Revit Course. My Query is structured like this:

Select * From StudentData 
Where (CourseName LIKE 'AutoCAD%') 
AND NOT EXISTS (Select * From StudentData Where (CourseName LIKE 'Revit%'));

Doing the inverse of the EXISTS does give me a list of who studied both AutoCAD + Revit, but the NOT does not return any value.

Any Ideas?

Upvotes: 0

Views: 74

Answers (3)

Rahul Jain
Rahul Jain

Reputation: 1399

Try this:

Select 
    * 
From 
    StudentData sd
Where 
    (
      CourseName LIKE 'AutoCAD%'
    ) 
AND NOT EXISTS 
    (
        Select 
            * 
        From 
            StudentData sd2 
        Where 
            sd2.CourseName LIKE 'Revit%' 
        AND sd.StudentName = sd2.StudentName
    )

You missed out the id comparison from both the tables. Since there will at least 1 student with 'Revit%' course, NOT EXISTS condition will always fail for all student.

*****Demo(with sample data)*****

http://sqlfiddle.com/#!9/9a0c05/2

Upvotes: 4

Amit Prajapati
Amit Prajapati

Reputation: 1190

Please try below one

Select * From StudentData sd1
Where CourseName LIKE 'AutoCAD%'
AND NOT EXIST (SELECT * FROM From StudentData sd2 WHERE sd1.Id = sd2.Id and CourseName LIKE 'Revit%')

Upvotes: 0

apomene
apomene

Reputation: 14389

Use NOT IN, instead. Try:

  SELECT *
FROM StudentData
WHERE (CourseName LIKE 'AutoCAD%')
    AND CourseName NOT IN (
        SELECT CourseName
        FROM StudentData
        WHERE (CourseName LIKE 'Revit%')
        )

Upvotes: 2

Related Questions