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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

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

Post 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.
User avatar
jackpf
DevNet Resident
Posts: 2119
Joined: Sun Feb 15, 2009 7:22 pm
Location: Ipswich, UK

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

Post by jackpf »

Not sure if this would work,

But you could try:

[sql]IFNULL((firstquery), (second query))[/sql]
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

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

Post 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.
Post Reply