I am running a SQL query to obtain results with the column applicationstatus that aren't S to tell which position is still open. There are 3 application status U=Unsuccessful, O = ongoing and S = successful. This works fine. Below is the code I am running.
SELECT DISTINCT position.position_ID, Title, EmployerName, Industry
JOIN application ON position.position_id = application.position_id
JOIN employer ON position.employer_id = employer.employer_id
WHERE applicationstatus != 'S'
NOT EXISTS condition to exclude
positions with successful applications:
SELECT DISTINCT p.position_ID, Title, EmployerName, Industry FROM position p JOIN application a ON p.position_id = a.position_id JOIN employer e ON p.employer_id = e.employer_id WHERE applicationstatus != 'S' AND NOT EXISTS( SELECT 1 FROM application WHERE position_id = a.position_id AND applicationstatus = 'S' )
Note that I've rewritten your query to use meaningful aliases. You should also do this to improve readability and maintainability of your code.