How to link a SQL table in a Subquery with Knex

17 views Asked by At

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.

0

There are 0 answers