I al ready have the query develop on SQL and it works properly but it did not work on Knex syntax, I am following the documentation but it did not works.
SQL Syntax:
SELECT
t2.*
FROM
table1 AS t1
LEFT JOIN table2 AS t2 ON t2.Id = t1.Id
WHERE
t1.Id_Status = 2
AND t2.Discount_Price > '0'
AND NOT EXISTS (SELECT * FROM table3 AS t3 WHERE t3.Id_App = 1 AND t3.Id_Record = t1.Id AND t3.Id_Status = 8)
KNEX Syntax:
return await connection('table1 AS t1')
.leftJoin('table2 AS t2', 't2.Id', 't1.Id')
.where('t1.Id_Status', 2)
.andWhere('t2.Discount_Price', '>', '0')
.whereNotExists(
connection('table3')
.select('Id_Record')
.where('Id_App', 1)
.andWhere('Id_Status', 8)
.as('t3'),
't3.Id_Record',
't1.Id'
)
.select('t2.*');
If I use the .as('t3')
I have the next error message:
Incorrect syntax near the keyword 'as'."
If I remove the alias for the table and include into the same connection('table3 AS t3')
it did not show an error, but it does not return anything.