ilovetaufu ilovetaufu - 1 year ago 90
MySQL Question

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?

Answer Source

Add a NOT EXISTS condition to exclude positions with successful applications:

FROM position p
JOIN application a
    ON p.position_id = a.position_id
JOIN employer e
    ON p.employer_id = e.employer_id
    applicationstatus != 'S'
        SELECT 1
        FROM application
            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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download