How to convert SQL Server query to MySQL (OUTER APPLY)

Question

I'm trying to switch from SQL Server to MySQL, hence some queries need to be converted.

The following SQL Server query gives my desired output:

SELECT top 20 c.ID as Pid, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Id, p.Debit, p.Credit
FROM TblCustomer c OUTER APPLY
(
SELECT TOP 1 p.*
FROM TblPayments p
WHERE c.CustomerNumber = p.CustomerNumber
ORDER BY p.id DESC
)
p

The output is in the following <link: https://drive.google.com/file/d/10n0VJn59OlzT-pyVtXXx197EAuStBvwh/view?usp=sharing>

I am trying the following MySQL query, however it is not what I am looking for:

SELECT c.CustomerNumber, c.ID, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Id, p.Debit, p.Credit
FROM TblCustomer as c
join TblPayments as p
on c.CustomerNumber = p.CustomerNumber
order by p.Id DESC
limit  20

The output of MySQL is the following: https://drive.google.com/file/d/11sKHPt3xI6qKUdF3bCL_kZGr8EeX7pts/view?usp=sharing

Description of output: Each customer from TblCustomer (top 20) with its respective Id from TblPayment (latest invoice Id).

Can someone help me to convert the first query (SQL Server) to MySQL query?

Answer 1

Following the answer from @Lukasz Szozda from the link https://stackoverflow.com/a/54467105/14405988 and the suggestion from @Akina I reached my desired outcome with the following MySQL query:

SELECT c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Id, p.Debit, p.Credit
FROM TblCustomer c, LATERAL
(
SELECT p.*
FROM TblPayments p
WHERE c.CustomerNumber = p.CustomerNumber
ORDER BY p.id DESC
LIMIT 1)
p
limit 20