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

The if statement is only allowed in stored programs. YOu might be able to set up what you want as a single query, but the other code is a mystery. — Gordon Linoff
– Gordon Linoff, Commented Aug 4, 2014 at 14:38

jan · Accepted Answer · 2014-08-04 15:42:19Z

4

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.

answered Aug 4, 2014 at 15:42

jan

934 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Ruddell · Accepted Answer · 2014-08-04 15:26:12Z

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

edited Aug 4, 2014 at 15:26

answered Aug 4, 2014 at 15:21

John Ruddell

25.9k7 gold badges60 silver badges88 bronze badges

2 Comments

Greylegface Over a year ago

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

John Ruddell Over a year ago

@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 :)

Caleth · Accepted Answer · 2014-08-04 15:39:02Z

0

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 *

answered Aug 4, 2014 at 15:39

Caleth

66k2 gold badges53 silver badges101 bronze badges

Collectives™ on Stack Overflow

SQL IF SELECT query is null then do another query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related