SQL IF SELECT query is null then do another query

Question

I have a query that regularly returns "nothing", and I would like to run a different query if this is the case, but I know not of the way of doing this. If anyone could be of help please.

Here is the current code I am using...

SELECT * FROM cfg_users JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid WHERE iTeamId='0' AND sDisabled IS NULL AND iStatusId > 0 AND sDate = '2014-08-01' GROUP BY cfg_users.iUserId ORDER BY iStatusId, sName

I basically want to say

IF <my code> IS NULL THEN <do other code>, IF <my code> IS NOT NULL THEN return the result.

Thanks

Solution 1

A way you can do it is like this

• set two variables equal to the queries you want to execute.

• set another variable equal to the correct query when the first is not null.

• execute that query with a stored procedure.

STORED PROCEDURE:

DELIMITER $$

CREATE PROCEDURE `dynamic_query`(in input varchar(255))
BEGIN
    SET @a := input;
    PREPARE stmt FROM @a;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
$$

DELIMITER ;

THE TWO SELECTS YOU WANT TO EXECUTE:

SET @A := "SELECT * FROM  cfg_users JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid WHERE iTeamId='0' AND sDisabled IS NULL AND iStatusId > 0 AND sDate = '2014-08-01' GROUP BY cfg_users.iUserId ORDER BY iStatusId, sName";
SET @B := "your other select here";

THE DEFINER TO GET THE CORRECT QUERY:

SET @C := (
SELECT
    CASE 
        WHEN EXISTS
            (   SELECT * 
                FROM  cfg_users 
                JOIN cfg_ash ON cfg_users.iUserId = cfg_ash.iUserid 
                WHERE iTeamId='0' 
                    AND sDisabled IS NULL 
                    AND iStatusId > 0 
                    AND sDate = '2014-08-01' 
                GROUP BY cfg_users.iUserId 
                ORDER BY iStatusId, sName
            )
        THEN @A
        ELSE @B
    END
);

EXECUTE THE STATEMENT:

CALL dynamic_query(@C);

• DEMO WHEN THE QUERY EXISTS
• DEMO WHEN THE QUERY DOESN'T EXIST

Solution 2

There are some simple way only use sql.

Define your first query as a temp table, with union all, filter the second query with temp table's count.

with temp as (select * from t1 where 1=0)
select * from temp
union all
select * from t2 where (select count(*) from  temp) =0

This query will return the second table's records.

with temp as (select * from t1 )
select * from temp
union all
select * from t2 where (select count(*) from  temp) =0

And if temp query have result, only return temp query.

You can test with sql fiddle here.

Comments

[Greylegface]

Wow, did not know this was a possibility. I shall have a go at this and see what happens. Thanks John Ruddell.

[John Ruddell]

@Greylegface you're welcome! Look at the demos to see it in action.. the trick is you need to set up the stored procedure and save it to the database once you do that this is a simple query :)