CODEWITHSUNDEEP
mysqlsql
Cem Baykam
Cem Baykam

Reputation: 537

Another select statement if the value is NULL

I want to send completely another query if the value of the given field is NULL is it possible ?

Something like this : 

   IF the value is null 
      SELECT * FROM `locations`
   ELSE 
      SELECT * FROM `companies`
   ENDIF

I want to use the output of this query in another query. So it is going to be like this. If the field is not null in the first table take it else take another field from another table and lastly another query from the result of this if.

Upvotes: 3

Views: 5967

Answers (4)

wildplasser
wildplasser

Reputation: 44240

Similar to aleroot's contribution but avoiding the (IMHO) ugly case: (also untested)

SELECT * FROM (SELECT * FROM locations WHERE value IS NULL)
UNION ALL
SELECT * FROM (SELECT * FROM companies WHERE value IS NOT NULL)

Upvotes: 2

Paulo H.
Paulo H.

Reputation: 1258

If i understand, you want to retrive one, or another query, with different table definitions, in one call, if something is null? i think is not a good practice...

Using MysqlI and a stored procedure, you can read the last select executed, but is weird:

DELIMITER //
CREATE PROCEDURE make_weird_query() BEGIN
   IF something is null THEN
      SELECT * FROM `locations`;
   ELSE 
      SELECT * FROM `companies`;
   ENDIF;
END;//

In PHP:

$query = $mysql->query("CALL make_weird_query()");
while($row = $query->fetch_assoc()){
   // ...
}

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112324

SELECT
    CASE WHEN L.value IS NULL THEN C.x ELSE L.x END AS x,
    CASE WHEN L.value IS NULL THEN C.y ELSE L.y END AS y,
    CASE WHEN L.value IS NULL THEN C.z ELSE L.z END AS z,
  ...
FROM
    locations L
    LEFT JOIN companies C
        ON L.LocationID=C.LocationID;

I do not know how you will have to join your tables in your case, but this gives you an idea of how you can solve the problem. In my solution, the decision whether the fields are taken from locations or companies, is taken on a record-by-record basis.

Upvotes: 0

aleroot
aleroot

Reputation: 72636

 SELECT * FROM `locations` WHERE 1 = (CASE WHEN value IS NULL THEN 1 ELSE 0 END)
 UNION ALL
 SELECT * FROM `companies` WHERE 1 = (CASE WHEN value IS NOT NULL THEN 1 ELSE 0 END)

not tested but should work.

Upvotes: 4

Related Questions