php - mysql select total which don't exist -

i try count of missing translations , getting lost in how it.

tables:

• languages (language_id, name)
• products (product_id)
• product_translations (product_id, language_id, name)

the admin getting lazy , want able show them count of how many translations missing.

i guess simple of doing total (languages->count * products->count) wanted return count each product separately.

to such query, start driver table (subquery) has combinations. remove ones have translations:

select driver.* (select distinct l.language_id, p.product_id       languages l cross join            products p      ) driver left outer join      translations t      on t.language_id = driver.language_id ,         t.product_id = driver.product_id t.language_id null; 

this uses left outer join, keeps in driver table. if there no match, columns in translations null -- where clause keeps these.

the distinct may not necessary in subquery, if values in each table unique.

as note: above preferred way write query, because think clearest in intent. but, mysql materializes subquery. following more efficient, if columns unique in 2 reference tables:

select l.*, p.* languages l cross join      products p left outer join      translations t      on t.language_id = l.language_id ,         t.product_id = p.product_id t.language_id null;