0

I have 2 statements that work fine alone when talking to MySQL db. Both stored in variables like aSQL and bSQL.

So i'm trying to do the following where i return aSQL if any records from the query exist, else return the results from bSQL.

let sql = `
IF (${aSQL}) THEN
BEGIN
    ${aSQL};
END;
ELSE
BEGIN
    ${bSQL};
END;
END IF;
`

I also tried the following from another SO post (replaced WHERE with AND because my query already has a WHERE):

let sql = `
with temp as (${aSQL})
select * from temp
union all
${bSQL} AND (select count(*) from  temp) =0
`

And even:

let sql = `SELECT IF(EXISTS(${aSQL}), 1, 0) ELSE ${bSQL}`

No mater what, i get You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near.

But if i simply do connection.query(aSQL) or connection.query(bSQL) it works.

How do you do this in a single query without permanently storing a procedure in the db?

Note for non-js devs sql = `${aSQL}` is just a template and inputs full string value of var aSQL that might be equal to SELECT * FROM db.table WHERE id=60 AND other=foo.

jtlindsey
  • 4,346
  • 4
  • 45
  • 73

2 Answers2

1

Found the solution here:

let sql = `${aSQL} UNION ALL ${bSQL} AND NOT EXISTS (${aSQL})`

Where aSQL and bSQL look similar to the following:

SELECT * FROM db.table WHERE id=60 AND other=foo
SELECT * FROM db.table WHERE id=47 AND other=bar
jtlindsey
  • 4,346
  • 4
  • 45
  • 73
0

 just wait for result, and if result is empty, execute next query:

connection.query(aSQL, function (err, result, fields) {
    if (err || result == 0 || result.length == 0) {
      connection.query(bSQL, function(err, result, fields){console.log(result)});
    }else {
      console.log(result)
    }    
 });
Roman Habibi
  • 604
  • 5
  • 8
  • I realize that's possible. My question was to find out how to do it in single query. – jtlindsey Oct 25 '17 at 23:43
  • It returns `ER_OPERAND_COLUMNS: Operand should contain 1 column(s)` If i remove the extra `()` it returns same as my original results. I think all `IF` statements might require Stored Procedure – jtlindsey Oct 26 '17 at 01:02