0
SELECT C.*, cs.name as status_name, ICR.status, ICR.message
FROM customer C
LEFT JOIN customer_status AS cs ON cs.cname = C.status
LEFT JOIN requests as ICR ON ICR.id = (
    SELECT ICRM.customer_id FROM requests ON ICRM 
    WHERE ICRM.customer_id = C.id 
    ORDER BY ICRM.id DESC 
    LIMIT 1 )
WHERE {$whereDate} {$Query}
GROUP BY C.id ORDER BY C.date_add ASC LIMIT 6

this line is causing the error

LEFT JOIN requests as ICR ON ICR.id = (SELECT ICRM.customer_id FROM requests ON ICRM WHERE ICRM.customer_id = C.id ORDER BY ICRM.id DESC LIMIT 1 )

When I add the reques table the data is not working. I want to pull only the last data for each customer from the questions table.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Yener Polat
  • 29
  • 1
  • 4
  • 1
    What is `FROM requests on ICRM` supposed to mean? `ON` can only be used in a `JOIN` clause to specify the relationship between the tables being joined. – Barmar Aug 06 '21 at 19:09
  • And what is the `ICRM` table? It's never mentioned in any `FROM` or `JOIN` clauses. – Barmar Aug 06 '21 at 19:10
  • See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?noredirect=1&lq=1 for how to get the last row for each customer from a table. – Barmar Aug 06 '21 at 19:11
  • `LEFT JOIN (SELECT customer_id, message MAX(id) id FROM requests Group by id) ICR ON C.id = ICR.customer_id` edited but not working – Yener Polat Aug 06 '21 at 19:35

0 Answers0