There is a list of products where there is a title and desc field. And there is another table with translations: title , desc , key . Where key is the user's language (ru, en, etc.). As a single request to pull out a list of all products and glue it with translations, if any. That is, goods with a common id are combined. For example, a request with the condition en, for all goods where there is a translation, the value of title and desc will change, and in other cases it will remain from the list of goods. Also relevant are recommendations on the organization of another structure.

    1 answer 1

     select coalesce(t2.title,t1.title) as title, coalesce(t2.desc,t1.desc) as desc from t1 left join t2 on t1.id=t2.id and t2.key = 'en'

    coalesce

    • If I finish condition WHERE t2 . key = 'en', then returns only those elements that have a translation. That is, 5 transfers, and 7 goods, for example. Returns 5 - Fortael
    • one
      @Fortael needs to be added and t2.key = 'en' in the join table, and not in the general WHERE condition. - vikolyada
    • @ VitalyKolyada, this is perfect. The only caveat that access to the fields can now be obtained only this way: $ query [0] ['coalesce (lang.title, shop.title)'] - Fortael
    • one
      @Fortael I think this can be cured by specifying coalesce(t2.title,t1.title) as title , coalesce(t2.desc,t1.desc) as desc in select - vikolyada