How to run a second sql select if the first is empty?

Question

I've been searching all over for the answer, I discovered about CASE, IFF and UNION but I haven't been able to figure it out, below is what I've been trying.

SELECT *
    FROM car_label as a
    INNER JOIN car as b
    ON a.label_id = b.label_id
    WHERE a.car_id = 619
UNION ALL
SELECT *
    FROM car
    WHERE car_id = 619

If the first select returns empty, I'd like to run the second select instead. This first part works perfectly, SELECT * FROM car_label as a INNER JOIN car as b ON a.label_id = b.label_id WHERE a.car_id = :car_id, I'm just trying to include the second select in case the first is empty.

Update:

Message: 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (SELECT * FROM car_label as a INNER JOIN car as' at line 1'

score 3 · Answer 1 · answered Sep 23 '17 at 08:25

Try this:

SELECT *
FROM car_label as a
INNER JOIN car as b ON a.label_id = b.label_id
WHERE a.car_id = 619
UNION ALL
SELECT *
FROM car
WHERE car_id = 619
  AND NOT EXISTS (SELECT 1 
                  FROM car_label as a
                  INNER JOIN car as b ON a.label_id = b.label_id
                  WHERE a.car_id = 619)

So if the first query returns any data, then the NOT EXISTS will be true and therefore the second query condition will be true and the vise versa.

Note that, in order for this query to work the columns returned should have the same count and the same data type, so you might need to list the columns instead of *.

However, I think you might need to try LEFT JOIN so if there is no matching cars in car_label you will still get the data from car table:

SELECT b.*
FROM car as b
LEFT JOIN car_label as a ON a.label_id = b.label_id AND a.car_id = 619

Vignesh Kumar A · Accepted Answer · 2017-09-23T08:46:45.550

2

I am not pretty sure about your query. As far as my understanding, you can go for IF EXISTS

Try Below

MSSQL

 IF EXISTS(SELECT * FROM car_label as a INNER JOIN car as b ON a.label_id = b.label_id WHERE a.car_id = 619) 
 BEGIN 
        SELECT *
        FROM car_label as a INNER JOIN car as b ON a.label_id = b.label_id
        WHERE a.car_id = 619
 END
 ELSE
 BEGIN
        SELECT *
        FROM car
        WHERE car_id = 619
 END

MySQL

 IF (SELECT * FROM car_label as a INNER JOIN car as b ON a.label_id = b.label_id WHERE a.car_id = 619) THEN
 BEGIN 
        SELECT *
        FROM car_label as a INNER JOIN car as b ON a.label_id = b.label_id
        WHERE a.car_id = 619
 END;
 ELSE
 BEGIN
        SELECT *
        FROM car
        WHERE car_id = 619
 END;
 END IF;

edited Sep 23 '17 at 08:46

answered Sep 23 '17 at 08:31

Vignesh Kumar A

27,863
13
63
115

Just something I've been receiving when I tried IF's in the pdo prepare, any idea?: Message: 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT * FROM car_label as a ' at line 1' – Stark Sep 23 '17 at 08:40
Are you using SQL Server or MYSQL? – Vignesh Kumar A Sep 23 '17 at 08:44
I'm using MySQL. – Stark Sep 23 '17 at 08:46
Thanks, I was just reading over https://stackoverflow.com/questions/5528854/usage-of-mysqls-if-exists, I'll give it a go. – Stark Sep 23 '17 at 08:47
Weird though, I'm still receiving the same syntax error. – Stark Sep 23 '17 at 08:50

How to run a second sql select if the first is empty?

2 Answers2