MySQL – Retrieve data conditionally

This is a MySQL Interview Question..

Based on the MySQL tables below, type one query that will retrieve data into 3 columns:

1st column: customer’s name

2nd column: phone number (if ‘home_phone’ is not provided, this should contain ‘cell_phone’, and vice versa)

3rd column called effective_province: the province in the group they belong to, so long as it is being marketed, or otherwise it would contain the customer’s own province instead. The query should return only rows whereby effective_province is “BC”, in alphabetical order by customer name.

Customers Table

Solution

select c.name as `customer's name`, if(c.home_phone!='',
c.home_phone,c.cell_phone) as 'phone number', 
if(g.marketed='Y',g.province,c.province) as 'effective_province' 
from customers c left join groupss g on c.group_id=g.id 
where g.province='BC' order by c.name;