Error syntax "LIMIT" subquery with inner join-CodePudding

I'm trying to do a subquery. For some purposes (i have to manipulate data with the js library DataTables), i need to put a subquery in my query. But when i try to do this, i get a syntax error.

Essentially, i have two tables:

TABLES

WW_PS_ORDERS

| id_order| reference| total_paid| date_add| id_cart|

|:------- |:--------:| ---------:| -------:| ------:|

| 1       | ABCDEF   | 10        |2022-01-01| 15

SMARTY

| ID_PS| identificativo_ordine|

|:-    |:--------------------:| 

| 15    |  ABCDJEHR          |

So, for get all fields of first table, and the field 'identificativo_ordine' of second table, i made this request:

SELECT en.id_order, en.reference, en.total_paid, en.date_add, 
        s.identificativo_ordine, en.id_cart
from develettronw.ww_ps_orders en 
    inner join smarty.orders s on en.id_order = s.id_ps
order by en.date_add desc

But ideally i need to do something like that:

SELECT id_order, 
        reference,total_paid,date_add,identificativo_ordine,id_cart 
FROM (
        SELECT en.id_order, en.reference, en.total_paid, 
                en.date_add, s.identificativo_ordine, en.id_cart
        from develettronw.ww_ps_orders en 
            inner join smarty.orders s on en.id_order = s.id_ps
      )

But i get a "syntax error" caused by LIMIT, but i have no LIMIT in my request. Why?

#1064 - Errore di sintassi nella query SQL vicino a 'LIMIT 0,25' line 5

.. which roughly translated means

#1064 - Syntax error in sql query near 'limit 0 25' line 5

CodePudding user response：

Your MySQL client is trying to paginate results automagically. The query is not valid in the first place, and the paginated query is not valid either. Your query would throw:

Every derived table must have its own alias

... because you forgot to define an alias for the subquery. It should be:

SELECT id_order, 
        reference,total_paid,date_add,identificativo_ordine,id_cart 
FROM (
        SELECT en.id_order, en.reference, en.total_paid, 
                en.date_add, s.identificativo_ordine, en.id_cart
        from develettronw.ww_ps_orders en 
            inner join smarty.orders s on en.id_order = s.id_ps
      ) some_alias_here --> You missed this

Appending LIMIT has the side effect of masking the actual error.