CODEWITHSUNDEEP
mysqlsqlcoalesce
user1661454
user1661454

Reputation: 35

coalesce problems

i have 2 tables for example

user_table                    level_table
==================           ===================
| name | levelid |           | id | levelname  |
==================           ===================
| tom  |    1    |           | 1  |  beginner  |
------------------           -------------------
| jon  |    2    |           | 2  |  intermed  |
------------------           ------------------- 
| tom  |  null   |           | 3  |   expert   |
------------------           -------------------
| jon  |    1    |          
------------------

i need to select user table with levelname only where if null, it will show my defined text such 'no level'. and there's no identical column header. so it would appear like the left one instead of this right one.

Right result                      Wrong result
==================               ================== 
| name |   level  |              | name |   level  | 
==================               ================== 
| tom  | beginner |              | tom  | beginner | 
------------------               ------------------ 
| jon  | intermed |              | jon  | beginner |
------------------               ------------------ 
| tom  | no level |              | tom  | beginner |
------------------               ------------------ 
| jon  | beginner |              | jon  | beginner |
------------------               ------------------

i've tried coalesce but seems there's a mistake where all levelname rows shows the same result.

SELECT name,
       COALESCE(
               (SELECT levelname FROM user_table,level_table WHERE levelid=id),
               'no level') AS level
       FROM user_table

Upvotes: 2

Views: 503

Answers (1)

John Woo
John Woo

Reputation: 263713

It should be like this, you need to join both tables using LEFT JOIN 

SELECT  a.name, COALESCE(b.levelname, 'no level') levelname
FROM    user_table a
        LEFT JOIN level_table b
            ON a.levelid = b.id

The difference between LEFT JOIN and INNER JOIN is that LEFT JOIN return all rows on the left table whether it has a match on the right table or not while INNER JOIN only return rows that are present on both tables.

Visual Representation of JOINS

Upvotes: 6

Related Questions