Vahn Vahn - 5 months ago 9
SQL Question

Return default data from second query if the first query returns NULL

I have a select query. How can I set the result of my select which select not exist item to 0.

Here's an example table:

Name Purchase
Vahn TV
Vahn SmartPhone
Ashe SmartPhone
Vlad TV
Vlad Notebook
Vlad SmartPhone


What i'd like to do, when I select non existing value in the table like this:

SELECT Purchase FROM example WHERE Name = 'Balthier'


My expected result is :

Name Purchase
Balthier -


and vice versa

SELECT Name FROM example WHERE Purchase = 'Drone'


My expected result is :

Purchase Name
Drone -


Is it possible in MySQL query only? Or there are better suggestion. Any hel is greatly appreciated. Thank You

nb:

I've tried IFNULL and COALESCE, but still have no luck :(

Answer

In order to get the first expected result you may try the following query:\

SELECT 
Name,
Purchase 
FROM example 
WHERE Name = 'Balthier'

UNION 

SELECT 
*
FROM 
( SELECT 'Balthier','-' ) AS t
WHERE FOUND_ROWS() = 0;

In order to get the second expected result try this:

SELECT 
Purchase,
Name 
FROM example 
WHERE Purchase = 'Drone'

UNION 

SELECT 
*
FROM 
( SELECT 'Drone','-' ) AS t
WHERE FOUND_ROWS() = 0;

ALTERNATIVE APPROACH:(using NOT EXISTS)

SELECT 
Purchase,
Name 
FROM example 
WHERE Purchase = 'Drone'

UNION 

SELECT 
*
FROM 
( SELECT 'Drone','-' ) AS t
  WHERE NOT EXISTS (
        SELECT 
        *
        FROM example 
        WHERE Purchase = 'Drone'
)

SQL FIDDLE DEMO

Comments