CODEWITHSUNDEEP
mysqldatabasecase
Stark
Stark

Reputation: 593

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

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'

Upvotes: 0

Views: 2222

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

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;

Upvotes: 2

user8608099
user8608099

Reputation:

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

Upvotes: 3

Related Questions