Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
891 views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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 ;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...