CODEWITHSUNDEEP
phpmysql
Harshwardhan Sharma
Harshwardhan Sharma

Reputation: 261

how to get the record of default value

I'm working on the database design of a multilingual website. I want to use the following popular method:

postid  |   post_title  |  lang
------------------------------
1       |  title1        |  en
1       |  title2        |  fr
2       |  title3        |  en

When i change the language i run a query to get the post like 

select post_title from post where lang = "en" group by postid

postid  |   post_title  |  lang
------------------------------
1       |  title1        |  en
2       |  title3        |  en


select post_title from post where lang = "fr" group by postid

postid  |   post_title  |  lang
------------------------------
1       |  title2        |  fr

The problem is if i want a query which give me the result like

 postid  |   post_title  |  lang
------------------------------
1       |  title2        |  fr
2       |  title3        |  en

means if i don't get the post of fr language post then i should get default en language post.

Upvotes: 3

Views: 56

Answers (1)

AymDev
AymDev

Reputation: 7554

This query will return rows where lang = "fr" or rows where lang = "en" if no record has been found in "fr" for the corresponding postid.

SELECT
    *
FROM post AS a
WHERE
    a.lang = "fr"
    OR (
        a.lang = "en"
        AND (
            SELECT COUNT(*)
            FROM post AS b
            WHERE
                b.lang = "fr"
            AND b.postid = a.postid
        ) = 0
    )
GROUP BY a.postid

This attempt works but I can't tell if this is the best way to achieve it.

Upvotes: 1

Related Questions