CODEWITHSUNDEEP
mysql
Jablalr
Jablalr

Reputation: 305

MySql procedure returning variable result from different tables

Is it possible within a MySQL stored procedure to return the result of the first select that has a value?

For example, imagine I have 3 tables with identical columns. I want to run a select against each of them in order. If the first has results, then return those results only. If it does not, check the second etc...

So something like this in psuedo code!

if (select code from table1 where col='apples') then
    return these results
else (select code from table2 where col='apples') then
    return these results
else (select code from table3 where col='apples') then
    return these results
endif

I cannot use a UNION (I don't think) as I want to stop once I get a set of results. So, if I find a match after the first query, just return those results even though subsequent queries might return results too.

Thanks!

Upvotes: 0

Views: 25

Answers (1)

Krish
Krish

Reputation: 5917

You can use the count function to see whether any records are found and if yes, use the select. Encapsulate this logic within a block so you can leave at any time once a result set is found. You may also use prepared statements

your psuedo code would look like this in MySQL.

SearchBlock:Begin
    SET rCount = coalesce((select count(*) from table1 where col='apples'),0);
    IF (rCount > 0) THEN
        select code from table1 where col='apples';
        LEAVE SearchBlock;
    END IF;

    SET rCount = coalesce((select count(*) from table2 where col='apples'),0);
    IF (rCount > 0) THEN
        select code from table2 where col='apples';
        LEAVE SearchBlock;
    END IF;

    SET rCount = coalesce((select count(*) from table2 where col='apples'),0);
    IF (rCount > 0) THEN
        select code from table2 where col='apples';
        LEAVE SearchBlock;
    END IF;
End SearchBlock;

Upvotes: 1

Related Questions