Make an alias column with custom string in MySQL

user_9657068 0
Viewed 447

I have a MySQL tables like this:

##customer##
+-----------+----+---------+
|customer_id|name|telephone|
+-----------+----+---------+
|     1     |Andi|+62932011|
|     2     |Boby|+62928291|
|     3     |Jane|+62932212|
|     4     |John|+62999021|
|     5     |Beth|+62999021|
|     6     |Noel|+62999021|
+-----------+----+---------+

##plus_membership##
+-----------------+-----------+-------+------------+
|plus_membership_id|customer_id|status |requested_at|
+------------------+-----------+-------+------------+
|        1         |     1     |   1   | 2018-11-01 |
|        2         |     2     |   0   | 2018-11-03 |
|        3         |     4     |   2   | 2018-11-04 |
|        4         |     6     |   1   | 2018-11-05 |
+------------------+-----------+-------+------------+

there are two tables in above structure, the first is the customer with customer_id as the primary key and the second one is the plus_membership which has foreign key customer_id, the plus_membership table is a table to show a request if customer request to become a plus member, status 1 means the customer is apporved to be plus member. I need to select the customer table and add alias column lets say the alias column name is membership , that shows only regular or plus , plus means the customer in plus_membership status is 1, and regular if customer doesnt exist in plus_membership table or status is not 1 in membership table. for example:

SELECT *, .... AS membership FROM customer;

+-----------+----+---------+----------+
|customer_id|name|telephone|membership|
+-----------+----+---------+----------+
|     1     |Andi|+62932011|   Plus   |
|     2     |Boby|+62928291|  Regular |
|     3     |Jane|+62932212|  Regular | 
|     4     |John|+62999021|  Regular |
|     5     |Beth|+62999021|  Regular |
|     6     |Noel|+62999021|   Plus   |
+-----------+----+---------+----------+
select join alias mysql
2 Answers

You can use Left Join between the two tables, and use Case .. When conditional expressions to evaluate membership accordingly.

Left Join would ensure that all the customer(s) from the customer table are considered, whether they have a corresponding matching row in the plus_membership table or not.

SELECT
 c.customer_id, 
 c.name, 
 c.telephone, 
 (CASE WHEN pm.status = 1 THEN 'Plus' ELSE 'Regular' END) AS membership
FROM customer AS c
LEFT JOIN plus_membership AS pm 
  ON pm.customer_id = c.customer_id

Another approach can be using Correlated Subquery and Exists(). Generally, this would be less efficient than Left Join approach.

SELECT 
  c.customer_id, 
  c.name, 
  c.telephone, 
  CASE WHEN EXISTS (SELECT 1 
                    FROM plus_membership AS pm 
                    WHERE pm.customer_id = c.customer_id AND 
                          pm.status = 1
                   )
       THEN 'Plus' 
       ELSE 'Regular' 
  END AS membership 
FROM customer AS c
user_24693080

We use EXISTS or IN to look up data in another table.

select customer_id, name, telephone,
  case when customer_id in (select customer_id from plus_membership where status = 1)
       then 'Plus' else 'Regular' end as membership
from customer
order by customer_id;
user_22707620
Related