Mysql - Inverse 3 table-query

Question

I have this query which gives me a list of firms (tblprov) with their corresponding category (tblrubro)

Both tables are related through a lookup table (tblprovxrubro)

SELECT p.id, p.name, r.idCat, r.rubroDesc FROM tblprov p
JOIN tblprovxrubro pr on p.id = pr.idFirm
JOIN tblrubros r on pr.idCat = r.idCat
WHERE p.id = 20

In this example, I have one firm related to 2 firm categories, so the query will give me this result

id     |  razonSocial |  idCat    |   catDesc  |
20     |    Firm 1    |     2     |Electronics |
20     |    Firm 1    |     3     | Software   |

What if I need to know the opposite, categories that are not related to that firm?

Cant figure it out

Answer 1

You can do a LEFT JOIN from the categories table to the junction table. Categories table will be the leftmost, since you want to consider all the categories. All the categories which do not have a corresponding row in the junction table for the pid = 20 will be your required result. We consider those rows using IS NULL comparison operator.

SELECT r.idCat, r.rubroDesc 
FROM tblrubros r 
LEFT JOIN tblprovxrubro pr 
  on pr.idCat = r.idCat AND 
     pr.idFirm = 20
WHERE pr.idCat IS NULL