CODEWITHSUNDEEP
mysqlsql
ilovetaufu
ilovetaufu

Reputation: 25

MySQL Ignoring Specific Rows?

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
FROM position
JOIN application ON position.position_id = application.position_id
JOIN employer ON position.employer_id = employer.employer_id
WHERE applicationstatus != 'S'

The problem I am facing is with the above query code, let's assume position_ID 3212 has received 3 applications, with one successful application; I get 2 results for the mentioned position(Excluding the successful one) like this.

enter image description here

Is there a way to filter it so that if a position already has a successful application, then the rows with the same position ID will be ignored?

Upvotes: 2

Views: 223

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Add a 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.

Upvotes: 2

Related Questions