I am try to get a count of all missing translations and getting really lost in how to do it.
Tables:
The admin are getting very lazy and I want to be able to show them a count of how many translations are missing.
I guess a very simple was of doing this would be to just get the total (languages->count * products->count) but I wanted to return a count for each product separately.
To do such a query, start with a driver table (subquery) that has all combinations. Then remove the ones that have translations:
select driver.*
from (select distinct l.language_id, p.product_id
from languages l cross join
products p
) driver left outer join
translations t
on t.language_id = driver.language_id and
t.product_id = driver.product_id
where t.language_id is null;
This uses a left outer join, which keeps everything in the driver table. If there is no match, then the columns in translations will be NULL -- the where clause keeps only these.
The distinct may not be necessary in the subquery, if the values in each table are unique.
As a note: the above is my preferred way to write the query, because I think it is the clearest in intent. But, MySQL actually materializes the subquery. So the following is more efficient, if the columns are unique in the two reference tables:
select l.*, p.*
from languages l cross join
products p left outer join
translations t
on t.language_id = l.language_id and
t.product_id = p.product_id
where t.language_id is null;