Page 1 of 1

If a SELECT statement returns an empty result set...

Posted: Thu Aug 27, 2009 9:12 pm
by JellyFish
Is there a way to return something else if a SELECT statement returns an empty result set? For example, I'm trying this:

Code: Select all

 
INSERT user_referrals
SELECT $userId, $userId, 0
UNION 
SELECT ancestor_id, $userId, depth+1 FROM user_referrals
WHERE descendant_id = $referralId
 
if the second SELECT statement returns an empty result set, I'd like another statement to be UNIONed with the first SELECT statement. This is completely understandable right?

How do I check if a statement returned an empty result set or not? Is an empty result set just NULL or is it something else? How can I return something if the other thing is NULL, but if it isn't return the other thing (e.g. in javascript, var foo = foo || bar)?

Thanks for reading. All help is appreciated.

Re: If a SELECT statement returns an empty result set...

Posted: Thu Aug 27, 2009 9:17 pm
by jackpf
Not sure if this would work,

But you could try:

[sql]IFNULL((firstquery), (second query))[/sql]

Re: If a SELECT statement returns an empty result set...

Posted: Thu Aug 27, 2009 9:23 pm
by JellyFish
Yea I was trying that, it doesn't work... :(

EDIT: I tried both:

Code: Select all

 
SET @statement1 = (SELECT ancestor_id, 8, depth+1 FROM user_referrals
WHERE descendant_id = 23);
SET @statement2 = (SELECT 0, 8, 1);
 
SELECT 8, 8, 0
UNION 
IF(@statement1, @statement2);
 
and

Code: Select all

 
SET @statement1 = (SELECT ancestor_id, 8, depth+1 FROM user_referrals
WHERE descendant_id = 23);
SET @statement2 = (SELECT 0, 8, 1);
 
SELECT 8, 8, 0
UNION 
CASE WHEN @statement1 THEN @statement1 ELSE @statement2;
 
And both return the mysql error:
#1241 - Operand should contain 1 column(s)
I don't know what this means.

EDIT: I think this means for the when I set the variables @statement1 and @statement2. I think you can't have a variable be assigned a result set as a value.