CODEWITHSUNDEEP
mysqljoin
CIRCLE
CIRCLE

Reputation: 4879

Select column if no records found in translation table

I have the following two tables:
characteristics

╔════╤═══════╗
║ id │ value ║
╠════╪═══════╣
║ 1  │ A     ║
╟────┼───────╢
║ 2  │ B     ║
╟────┼───────╢
║ 3  │       ║
╟────┼───────╢
║ 4  │ X     ║
╚════╧═══════╝

characteristics_translations

╔════╤════════════════════╤══════════╤══════════════╗
║ id │ characteristics_id │ language │ title        ║
╠════╪════════════════════╪══════════╪══════════════╣
║ 1  │ 3                  │ en       │ EN - Cookies ║
╟────┼────────────────────┼──────────┼──────────────╢
║ 2  │ 3                  │ fr       │ FR - Cookies ║
╟────┼────────────────────┼──────────┼──────────────╢
║ 3  │ 3                  │ de       │ DE - Cookies ║
╟────┼────────────────────┼──────────┼──────────────╢
║ 4  │ 4                  │ en       │ EN - Apples  ║
╟────┼────────────────────┼──────────┼──────────────╢
║ 5  │ 4                  │ fr       │ FR - Apples  ║
╚════╧════════════════════╧══════════╧══════════════╝

And I'm trying to create a query in MySQL where I select all characteristics with a join left to characteristics_translations in case there is a defined title in the translations table. If not, it should select the column value from characteristics.

This is what I've tried so far:

SELECT c.id, 
       [c.value OR ct.title] 
FROM   `characteristics` c 
       LEFT JOIN `characteristics_translations` ct 
              ON c.id = ct.characteristics_id 
WHERE  ct.language = 'de' 
        OR ct.language = NULL;

The output should be something like this:

{
  "0":{
    id: "1",
    title: "A"
  },
  "1":{
    id: "2",
    title: "B"
  },
  "2":{
    id: "3",
    title: "DE - Cookies"
  },
  "3":{
    id: "4",
    title: "X"
  },
}

Final Solution

SELECT c.id, COALESCE(ct.title, c.value) title 
FROM   `characteristics` c 
       LEFT JOIN `characteristics_translations` ct 
              ON c.id = ct.characteristics_id 
              AND ct.language = 'de'

Upvotes: 2

Views: 49

Answers (2)

Shadow
Shadow

Reputation: 34232

Use coalesce() to get the 1st non-null value from the list of parameters: 

SELECT c.id, 
       coalesce(ct.title, c.value) as title
FROM   `characteristics` c 
       LEFT JOIN `characteristics_translations` ct 
              ON c.id = ct.characteristics_id 
WHERE  ct.language = 'de' 
        OR ct.language = NULL;

Upvotes: 1

hasumedic
hasumedic

Reputation: 2167

Try COALESCE:

SELECT c.id, COALESCE(ct.title, c.value) 
FROM   `characteristics` c 
       LEFT JOIN `characteristics_translations` ct 
              ON c.id = ct.characteristics_id 
WHERE  ct.language = 'de' 
        OR ct.language = NULL;

It'll return the first non-null value from a given list of parameters.

Upvotes: 1

Related Questions