开发者

Two columns within an ISNULL function in MySql

开发者 https://www.devze.com 2023-03-18 03:44 出处:网络
I have two tables, one is for individuals and the other is for开发者_Go百科 their company. Each table has a column for locale, this is mandatory for the company, but not for the individual. The idea

I have two tables, one is for individuals and the other is for开发者_Go百科 their company.

Each table has a column for locale, this is mandatory for the company, but not for the individual. The idea seems to be that if the individual doesn't set a preference, they are assumed to be in the locale of their company.

I would like to select the locale for the individual, using the company default if the individual locale is null and I thought of doing the following (which I don't think is possible in MySql)...

SELECT
    ISNULL(individual.Locale, company.Locale) `Locale`
FROM
    individual
INNER JOIN
    company ON company.CompanyId = individual.CompanyId
WHERE
    individual.IndividualId = 1

Is there a nice way to do this - or am I just going to end up sending both Locale's back and making the decision in the code?


You can use the COALESCE() function which returns the first non-NULL value among its arguments. This function can also be used in most other RDBMS like SQL-Server, Oracle 9, Postgres 8:

SELECT
    COALESCE(individual.Locale, company.Locale) AS Locale
FROM
    individual
INNER JOIN
    company ON company.CompanyId = individual.CompanyId
WHERE
    individual.IndividualId = 1


You pretty much had it, what you want is IFNULL()

SELECT
    IFNULL(individual.Locale, company.Locale) `Locale`
FROM
    individual
INNER JOIN
    company ON company.CompanyId = individual.CompanyId
WHERE
    individual.IndividualId = 1


Try the CASE...WHEN...THEN statement

SELECT 
    (CASE
       WHEN individual.Locale IS NULL THEN company.Locale
       ELSE individual.Locale
    END) 'Locale'
FROM
    individual
INNER JOIN
    company ON company.CompanyId = individual.CompanyId
WHERE
    individual.IndividualId = 1
0

精彩评论

暂无评论...
验证码 换一张
取 消