SQL syntax error while getting the last records of the each user from the multiple tables, Also not getting the output

Question

I have three tables tbl_lead, tbl_documentsPickup and tbl_bankdata. In the tbl_lead table, I am inserting personal information. In the tbl_documentsPickup, I am inserting the document information which is more than one record for each user and In tbl_bankdata, Inserting the bank data which is more than one record for each user.

Now what I am doing is, I have to display the last record of each user from tbl_documentsPickup and tbl_bankdata. Also, there is a possibility that tbl_documentsPickup and tbl_bankdata can be empty for users.

tbl_lead

 lead_id | name | email       | mobile     | 
    1    | qwsd |cm@gmail.com | 1111111111 |
    2    | mjhd |qs@gmail.com | 2222222222 |
    3    | oiuy |gh@gmail.com | 3333333333 | 
    4    | qswe |4e@gmail.com | 4444444444 | 

tbl_documentsPickup

  doc_id | lead_id| d_pickupStatus| d_date_of_created   
    1    | 1      | 1             | 2019-08-29 00:53:33
    2    | 2      | 1             | 2019-08-29 12:40:14
    3    | 1      | 2             | 2019-08-29 14:14:54
    4    | 3      | 1             | 2019-08-29 14:26:22
    5    | 3      | 3             | 2019-08-29 15:35:03

tbl_bankdata

 bank_id |p_id| lead_id| d_fileStatus  | date_of_added 
    1    | 1  |   1    | 1             | 2019-08-30 00:53:33
    2    | 2  |   2    | 3             | 2019-08-30 12:40:14
    3    | 3  |   1    | 4             | 2019-08-30 11:14:54
    4    | 4  |   3    | 1             | 2019-08-30 12:26:22
    5    | 5  |   3    | 2             | 2019-08-30 12:26:22

I am using below query but I am getting the error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1

$query="
SELECT * FROM tbl_lead 
LEFT JOIN (
    SELECT d.* FROM tbl_documentsPickup d 
    INNER JOIN (
         SELECT lead_id, d_pickupStatus,  MAX(d_date_of_created) AS maxdt_doc
         FROM tbl_documentsPickup GROUP BY lead_id
    ) e ON d.`lead_id`=e.`lead_id` AND d.`d_date_of_created`=e.`maxdt_doc`
) as tbl_document 
LEFT JOIN (
    select lead_id, b_bankDoneStatus, max(date_of_added) as latest 
    from tbl_bankdata group by lead_id HAVING max(`date_of_added`) = latest 
 ) r on tbl_lead.lead_id=r.lead_id";
 $getQuery= $this->db->query($query);
 return $getQuery->result();

Answer 1

You can approach this with correlated subqueries in the on clause of the left joins, as follows:

select l.*, d.*, b.*
from 
    tbl_lead l
    left join tbl_documentsPickup d 
        on  d.lead_id = l.lead_id
        and d.d_date_of_created  = (
            select max(d_date_of_created) 
            from tbl_documentsPickup d1 
            where d1.lead_id = d.lead_id
        )
    left join tbl_bankdata b 
        on  b.lead_id = l.lead_id
        and d.date_of_added = (
            select max(date_of_added) 
            from tbl_bankdata b1 
            where b1.lead_id = b.lead_id
        )

For performance, you want indexes on:

tbl_documentsPickup(lead_id, d_date_of_created)
tbl_bankdata(lead_id, date_of_added)

In MariaDB 10.2 or higher, another option is to use window functions:

select l.*, d.*, b.*
from 
    tbl_lead l
    left join 
        ( 
            select 
                d.*, 
                row_number() over(partition by lead_id order by d_date_of_created desc) rn 
            from tbl_documentsPickup d
        ) d on d.lead_id = l.lead_id and d.rn = 1
    left join 
        (
            select 
                b.*, 
                row_number() over(partition by lead_id order by date_of_added desc) rn 
            from tbl_bankdata b
        ) b on b.lead_id = l.lead_id and b.rn = 1

Answer 2

This is your query. (I've removed the superfluous backticks.)

SELECT * 
FROM tbl_lead 
LEFT JOIN 
(
  SELECT d.* 
  FROM tbl_documentsPickup d 
  INNER JOIN 
  (
    SELECT lead_id, d_pickupStatus, MAX(d_date_of_created) AS maxdt_doc 
    FROM tbl_documentsPickup
    GROUP BY lead_id
  ) e ON d.lead_id = e.lead_id AND d.d_date_of_created = e.maxdt_doc
) as tbl_document 
LEFT JOIN 
(
  select lead_id, b_bankDoneStatus, max(date_of_added) as latest 
  from tbl_bankdata 
  group by lead_id 
  HAVING max(date_of_added) = latest 
) r on tbl_lead.lead_id = r.lead_id;

Here are the things you are doing wrong:

1. You are selecting d_pickupStatus in your e subquery. But as you are grouping by lead_id only, you get one d_pickupStatus per lead_id arbitrarily picked. That doesn't seem to make much sense.
2. You left outer join tbl_document, but there is no join condition. You are missing the ON clause. That should be the syntax error you are seeing, I guess.
3. Your r subquery makes no sense at all to me. First you are selecting an arbitrarily picked value again (b_bankDoneStatus). And then you use a HAVING clause where you say that a value shall match itself. (latest is your alias name for max(date_of_added), so max(date_of_added) = latest is always true.)

Here is one way to get the last entries per lead_id from tbl_documentspickup and tbl_bankdata.

select * 
from tbl_lead 
left join 
(
  select *
  from tbl_documentspickup
  where (lead_id, d_date_of_created) in
  (
    select lead_id, max(d_date_of_created)
    from tbl_documentspickup
    group by lead_id
  )
) latest_documentspickup using (lead_id)
left join 
(
  select *
  from tbl_bankdata 
  where (lead_id, date_of_added) in
  (
    select lead_id, max(date_of_added)
    from tbl_bankdata
    group by lead_id
  )
) latest_bankdata using (lead_id)
order by lead_id;