php - MySQL the whole query fails if result of sub query is NULL
Get the solution ↓↓↓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 phprand($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 theID > 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.
Answer
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
Answer
Solution:
Change all your queries to :
SELECT FirstName FROM patient WHERE ID > 11445 AND FirstName != '' UNION SELECT NULL FROM DUAL LIMIT 1
i.e. add UNION SELECT NULL FROM DUAL (in all your subqueries)
Answer
Solution:
You need to use LEFT OUTER JOIN:
Select
tblFirstName.FirstName,
tblLastName.LastName,
tblBirthDate.BirthDate,
tblLocation.Location
From
(SELECT FirstName FROM patient WHERE ID > 11445 AND FirstName != '' LIMIT 1) AS tblFirstName
LEFT JOIN
(
(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
)
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: the metadata storage is not up to date, please run the sync-metadata-storage command to fix this issue.
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.