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
精彩评论