Check if no results with mysql

Question

I would like to know if there is a way in mysql to check if the query produces some results and if no do execute query.
Example:

(SELECT * FROM table WHERE id=2) IF NO RESULT (SELECT * FROM table WHERE id=4)

EDIT

I need only one query, basically first I check if there a result with a param (ex status=0) if there is no result I would like to execute the same query with the param changed to status=2.
Hope it can help

MORE EDIT
Basically I have a table with operatorators and departments and another one with all the users, first I check if there is an available operator in the first table and it's not on holiday, if there is no result I will lselect and admin from the second table, but only if there is no operator availbable

MORE MORE EDIT
This query check if there is an operator available but it doesn't select the admin

query = "SELECT b.id  
        FROM ".$SupportUserTable." b
        INNER JOIN ".$SupportUserPerDepaTable." a
            ON b.id=a.user_id
        WHERE a.department_id=? AND b.holiday='0' AND a.user_id!=".$_SESSION['id']." 
        ORDER BY b.assigned_tickets,b.solved_tickets ASC LIMIT 1";

Lastest Solution
This is not exactly what I was looking for, but it works, I'm open to improvments to avoid the execution of two queries:

$query = "SELECT *
                FROM(
                        (SELECT b.id  
                            FROM ".$SupportUserTable." b
                            INNER JOIN ".$SupportUserPerDepaTable." a
                                ON b.id=a.user_id
                            WHERE a.department_id=? AND b.holiday='0' AND a.user_id!=".$_SESSION['id']." 
                            ORDER BY b.assigned_tickets,b.solved_tickets ASC LIMIT 1)
                    UNION
                        (SELECT id  
                        FROM ".$SupportUserTable."
                        WHERE  status='2' AND id!=".$_SESSION['id']." 
                        ORDER BY assigned_tickets,solved_tickets ASC LIMIT 1)
                    ) tab
                    LIMIT 1
            ";

http://stackoverflow.com/questions/1175217/sql-server-if-not-exists-usage — DarkBee, Jul 29 '13 at 07:50
@Strawberry Tables design? Because this is the best I can think, the user can be assigned to multiple department, so I decided to create a different table (user and user per department), the first keep track of the user status(operator, user or admin) and holiday and the second of the user associated department, I tought that add the administrator to the the second table isn't advantageous, because if I add a new department I have also to add every administrator to it. if you have any suggestion I'll be glad to listen — Razorphyn, Jul 29 '13 at 10:54
So a given user might be (simultaneously) an `operator` in one department and an `administrator` in another? — Strawberry, Jul 29 '13 at 11:19
Sorry, an user can be an operator(on multiple department) or an administrator (only global) — Razorphyn, Jul 29 '13 at 11:31
My logic is: I don't know if the site has got only one person(admin) or multiple operator+admin, so I applied this structure, however the admin can choose to move the ticket to another operator or admin — Razorphyn, Jul 29 '13 at 11:47

score 2 · Answer 1 · answered Jul 29 '13 at 07:55

2

SELECT * FROM table WHERE id = (
  SELECT id FROM table WHERE id IN (2,4) ORDER BY id LIMIT 1
)

answered Jul 29 '13 at 07:55

eggyal

122,705
18
212
237

@Dheed: I would advise against using `IF` in this query. Try to adapt using the technique I have shown above. – eggyal Jul 29 '13 at 08:34

score 1 · Answer 2 · answered Jul 29 '13 at 07:53

1

You can do like this

Select IF(
(SELECT count(*) FROM table WHERE id=2), (SELECT * FROM table WHERE id=2),(SELECT * FROM table WHERE id=4))

answered Jul 29 '13 at 07:53

Suresh Kamrushi

15,627
13
75
90

`IF()` can't return multiple columns. – eggyal Jul 29 '13 at 07:56

Prahalad Gaggar · Answer 3 · 2013-07-29T08:07:19.180

0

select t.*
from
(SELECT * FROM table 
WHERE id in (2,4)
LIMIT 1)t
order by t.id

edited Jul 29 '13 at 08:07

answered Jul 29 '13 at 07:49

Prahalad Gaggar

11,389
16
53
71

I don't think he wants to get both results – mirkobrankovic Jul 29 '13 at 07:51
Now there is a syntax error (no `FROM` clause in the outer query); and the subquery uses `LIMIT` without `ORDER BY`, which will still yield indeterminate results. – eggyal Jul 29 '13 at 07:57

Check if no results with mysql

3 Answers3