SQL - Display value in column when left join fails

Question

Let's say I want to join on 2 tables, one is filled like this.

| Category  |
|-----------|
| id | name |
|----|------|
|  1 | Foo  |
|  2 | Bar  |
|  3 | Baz  |

The other like this:

|   Page    |
|-----------|
| id | cat  |
|----|------|
|  1 | 0    |
|  2 | 1    |
|  3 | 3    |

As you can see cat 0 in the Page table is not present in the Category table. Our system is unfortunately like this and I can't add the category with id 0 to the Category table, due to other code.

Now comes the one million dollar question: Is it possible to join category.id on page.cat and set an if statement when page.cat equals 0 to show the category name as Default?

Possible duplicate of [How to do a FULL OUTER JOIN in MySQL?](https://stackoverflow.com/questions/4796872/how-to-do-a-full-outer-join-in-mysql) — Chrᴉz remembers Monica, Apr 23 '18 at 13:25
I expect that I can show a category name on the page with id 1 which equals `Default`. For example I show category `Foo` on page with id 2 — Frank Groot, Apr 23 '18 at 13:29

Juan Carlos Oropeza · Accepted Answer · 2018-04-23T13:46:59.717

7

If only 0 is the one missing do a left join and use COALESCE to decode the nulls.

SELECT Page.*, COALESCE(name , 'Default')
FROM Page
LEFT JOIN Category
  ON Page.cat = Category.id;

or

SELECT P.*, IFNULL(C.name , 'Default') as Name   
FROM Page P   
LEFT JOIN Category C ON P.cat= C.id

edited Apr 23 '18 at 13:46

answered Apr 23 '18 at 13:28

Juan Carlos Oropeza

47,252
12
78
118

2

This will work no matter how many missing values there are. It'll just return the same thing for all of them. Which may or may not be what the OP wants, but it's a good starting point. – Eric Brandt Apr 23 '18 at 13:31
1

@EricBrandt Yes, I just realize that. – Juan Carlos Oropeza Apr 23 '18 at 13:31

Rahul Jain · Answer 2 · 2018-04-23T14:01:13.460

2

Try this out:

SELECT 
    p.id, 
    p.cat,
    case when p.cat = 0 then "DEFAULT" ELSE c.name END AS cat_name
FROM 
    cat c RIGHT JOIN page p
ON c.id = p.cat
ORDER BY p.id

edited Apr 23 '18 at 14:01

answered Apr 23 '18 at 13:27

Rahul Jain

1,319
7
16

This doesnt work. the `p.cat = 0` doesnt have anyone to join to – Juan Carlos Oropeza Apr 23 '18 at 13:47

SQL - Display value in column when left join fails

2 Answers2