CODEWITHSUNDEEP
mysqlsqlselectnull
Greylegface
Greylegface

Reputation: 71

SQL IF SELECT query is null then do another query

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

Upvotes: 5

Views: 14961

Answers (3)

jan
jan

Reputation: 93

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.

Upvotes: 4

Caleth
Caleth

Reputation: 62636

You can store the results in a temporary table / table variable, and then check the count

e.g.

CREATE TABLE #Results ( --columns you need here )
INSERT INTO #Results 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 @Count = SELECT COUNT(*) FROM #Results

IF 0 = @Count THEN
INSERT INTO #Results -- Other Query Here

SELECT * FROM #Results

n.b. you should really specify what columns you want in both queries rather than using *

Upvotes: 0

John Ruddell
John Ruddell

Reputation: 25842

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);

Upvotes: 1

Related Questions