Search code examples
mysqlsqljoinmany-to-many

MySQL get rows not present in relation table

Practice -> Financier (Many to Many)

Practice -> Financier_Practice (Many to one).

Practice

practice_id
practice_name


Financier

financier_id
financier_name


Financier_Practice

financier_id
practice_id
enabled

I have some records in financier_practice, now I want to get all practices that are not yet assigned to a given financier.

Example, if I have 3 practices, and one assigned to a financier, then return the other two for the same financier.

What should I do?

I thought LEFT JOIN would do it, first selecting practices, but it would return all practices.

Solution 
  • SELECT p.*
FROM Practice p
LEFT JOIN Financier_Practice fp
    ON p.practice_id = fp.practice_id
WHERE fp.practice_id IS NULL AND
      fp.financier_id = 3     -- replace 3 with whatever value you want