-1

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Qëndrim Izairi
  • 17
  • 7
  • I can see that the problem here on MSSQL you're doing `top 20` while `ORDER BY p.Id DESC LIMIT 20` in MySQL - that is not really a top 20 in MySQL part. The top 20 is based on which column? That's where you should do your `ORDER BY column_name DESC LIMIT 20`. – FanoFN Mar 09 '22 at 03:46
  • Let me try and clarify a bit more @FanoFN . Top 20 is not important, I am doing top 20 for efficiency purposes. I need to get all the clients from the first table (TblCustomer) and their last invoiceId from table 2 (TblPayment). Meaning, one client, one row. I want to get the client + their last invoice, because from there I get their balance (debit/credit). – Qëndrim Izairi Mar 09 '22 at 04:13
  • 1
    What is **precise** MySQL version? OUTER APPLY in SQL Server == LATERAL JOIN in recent MySQl versions. – Akina Mar 09 '22 at 04:55
  • *The following MSSQL query gives my desired output* This is **NOT** a query, this is some code which composes it. Show final query text. – Akina Mar 09 '22 at 04:56
  • That's a lot simpler then. Depending on your MySQL version, you might need to do `JOIN` once or twice to get the result. You can check MySQL version by running `SELECT version();` – FanoFN Mar 09 '22 at 05:05
  • In your MySQL statement is missing an eqivalent for `SELECT TOP 1` in the inner statement. – h.m.i.13 Mar 09 '22 at 07:14
  • Precise MySQL version 8.0.28. I am using JDBCTemplate in Spring Boot app. The query in MSSQL is as per the updated question now. So if someone can help me how to compose the query in MySQL? – Qëndrim Izairi Mar 09 '22 at 17:47

1 Answers1

0

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
Qëndrim Izairi
  • 17
  • 7