MySQL the whole query fails if result of sub query is NULL

mysql, php, select, sql, subquery

Solution

Try this:

SELECT (SELECT FirstName FROM patient WHERE ID > 11445 AND FirstName != '' LIMIT 1) AS tblFirstName,
         (SELECT LastName FROM patient WHERE ID > 74964 AND LastName != '' LIMIT 1) AS tblLastName,
         (SELECT BirthDate FROM patient WHERE ID > 26360 LIMIT 1) AS tblBirthDate,
         (SELECT Location FROM patient WHERE ID > 68356 AND Location != '' LIMIT 1) AS tblLocation

Problem

I have a situation where I need to generate rows with random data out of the same table's valid data I have generated ID by php `rand($min, $max)` function with `$min = 1` and `$max = [SELECT MAX(ID) FROM patient] - 1` ``` Select tblFirstName.FirstName, tblLastName.LastName, tblBirthDate.BirthDate, tblLocation.Location From (SELECT FirstName FROM patient WHERE ID > 11445 AND FirstName != '' LIMIT 1) AS tblFirstName, (SELECT LastName FROM patient WHERE ID > 74964 AND LastName != '' LIMIT 1) AS tblLastName, (SELECT BirthDate FROM patient WHERE ID > 26360 LIMIT 1) AS tblBirthDate, (SELECT Location FROM patient WHERE ID > 68356 AND Location != '' LIMIT 1) AS tblLocation ``` Now in the `ID > 26360` from the above query 26360 is that random number and `>` is used to avoid the possibility if 26360 was deleted PROBLEM: if any of the sub query returns no result complete query fails and return nothing.

Original source