CODEWITHSUNDEEP
mysqlsqlleft-joininnodb
Pierre Roudaut
Pierre Roudaut

Reputation: 1073

MySQL: left join exclusion

I'm struggling with a MySQL request i cannot seem to find the right syntax. The design of my database is pretty simple: I have a table applicant, a table internship and a table application. When an applicant applies for an internship, I insert a row into the application table containing two foreign keys linking the applicant and the internship.

QUESTION: How can I get the list of all the internships an applicant has not applied for already, knowing the applicant's id?

SO FAR: I came up with this query, but that's not it:

SELECT internship.* FROM internship LEFT JOIN application
ON application.id_internship = internship.id_internship
WHERE application.id_applicant IS NULL OR application.id_applicant <> 42
GROUP BY application.id_internship

Any help would be much appreciated. Have a good day.

database design

Upvotes: 0

Views: 94

Answers (2)

Guffa
Guffa

Reputation: 700840

You should use the condition for the applicant id in the join and use the = operator, so that the joined records are the ones connected to the applicant, then filter out the internships where the join has a match:

SELECT
  internship.*
FROM
  internship
  LEFT JOIN application ON application.id_internship = internship.id_internship AND application.id_applicant = 42
WHERE
  application.id_applicant IS NULL

There is no need to group the query, as each internship occurs only once in the result.

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727067

You can use EXISTS operator to do it without a join, like this:

SELECT *
FROM internship i
WHERE NOT EXISTS (
    SELECT *
    FROM application a
    WHERE i.id_internship = i.id_internship AND application.id_applicant = 42
)

The query is self-explanatory: only internships for which the applicant with id of 42 did not apply will be selected.

Upvotes: 1

Related Questions