CODEWITHSUNDEEP
mysqlsql
Petrus Alli
Petrus Alli

Reputation: 15

Make SQL query select another result if first select doesn't exist?

I'm trying to make a query always return a result. Using 

SELECT art_price, art_header FROM 'signs' WHERE art_number=113

and if 113 doesn't exist I want the query to do

SELECT art_price, art_header FROM 'signs' WHERE art_number=111

I've tried my best with IFNULL and COALESCE and CASE but doesn't get it to work.

How am I supposed to do?

Seems like my php is messing up what @Felix Pemittan solved for me

<?php
include 'dbc.php';
$query = "SELECT 
    art_price, art_header, art_pic, art_row1, art_row2, art_row3, art_row4  
FROM `signs`
WHERE art_number = ?

UNION ALL

SELECT 
    art_price, art_header, art_pic, art_row1, art_row2, art_row3, art_row4 
FROM `signs`
WHERE 
    art_number = 111
    AND NOT EXISTS(
        SELECT 1
        FROM `signs`
        WHERE art_number = ?
    )";


if($stmt = $conn->prepare($query)){
    $stmt->bind_param('s', $_POST['art_number']);
    $stmt->execute();
    $stmt->bind_result($rowPrice, $rowHeader, $rowPic, $rowArt1, $rowArt2, $rowArt3, $rowArt4);


    while($stmt->fetch()){
        ?>

Upvotes: 0

Views: 279

Answers (4)

Blank
Blank

Reputation: 12378

Did you want this?

SELECT art_price, art_header
FROM `signs`
WHERE art_number in (113, 111)
ORDER BY art_number DESC
LIMIT 1

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can combine the two queries using UNION ALL and then add a NOT EXISTS clause to the second query:

SELECT 
    art_price, art_header 
FROM `signs`
WHERE art_number = 113

UNION ALL

SELECT 
    art_price, art_header 
FROM `signs`
WHERE 
    art_number = 111
    AND NOT EXISTS(
        SELECT 1
        FROM `signs`
        WHERE art_number = 113
    )

Upvotes: 0

Pallavkumar
Pallavkumar

Reputation: 1

Query:

IF EXISTS(SELECT 1 FROM 'signs' WHERE art_number=113) 
    SELECT art_price, art_header FROM 'signs' WHERE art_number=113 
ELSE 
    SELECT art_price, art_header FROM 'signs' WHERE art_number=111

Upvotes: 0

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

SELECT art_price, art_header FROM 'signs' WHERE  (art_number=113 or (art_number=111 and 
(SELECT art_price, art_header FROM 'signs' WHERE art_number=113) is  null))

Upvotes: 1

Related Questions