join - Combining two MySql queries into a single query based upon a condition

Question

I am using 5.7.20-enterprise-commercial and have a MySql query which looks as below:

SELECT name FROM jobTable where jobId IN (SELECT dataId from logTable
WHERE eValue ='1098hbfce');

If the above query returns a null value then I want to run the below query:

SELECT name FROM jobTable where procId IN (
SELECT dataId from logTable WHERE eValue ='1098hbfce');

The only difference between first and second query is that in the first query jobId is used. And, in the second query procId is used

Also, I don't want to run the second query if the first query returns non null value

I am using the following approach but it doesn't seem to working correctly

IF (SELECT name FROM jobTable where jobId IN (SELECT dataId from logTable
   WHERE eValue ='1098hbfce')) IS NOT NULL 
   EXECUTE (SELECT name FROM jobTable where procId IN (
   SELECT dataId from logTable WHERE eValue ='1098hbfce'));

The requirement is do it in the SQL query itself and without creating a stored procedure. There is no relationship between jobTable and logTable

question from:https://stackoverflow.com/questions/65941052/combining-two-mysql-queries-into-a-single-query-based-upon-a-condition

Answer 1

Using CASE expression:

SELECT name 
FROM jobTable 
where (CASE 
       WHEN jobId IN (SELECT dataId from logTable WHERE eValue ='1098hbfce')
       THEN 1
       WHEN procId IN (SELECT dataId from logTable WHERE eValue ='1098hbfce') 
       THEN 1 
      END) = 1 ;