0

I have three tables which are tbl_lead, tbl_documentsPickup and tbl_bankdata.

tbl_lead table is connected with tbl_documentsPickup with lead_id same with tbl_bankdata. I have personal information on tbl_lead table, document informiton on tbl_documentsPickup and bank infromation on tbl_bankdata. Bank information can have more then one records.

Now I have to display the records from all the tables so I am using joins and group by.

Why group by because of In the tbl_bankdata i have more than one record of the same user. So I have to group the lead_id.

I have 3 records in tbl_lead , 3 records in tbl_documentsPickup and 5 records on tbl_bankdata. I am getting the 3 records using group by which is correct.

SO my query is,

SELECT * 
  FROM tbl_lead 
  LEFT JOIN tbl_documentsPickup ON tbl_lead.c_id=tbl_documentsPickup.lead_id  
  LEFT JOIN (
             SELECT lead_id, b_bankDoneStatus, max(date_of_added) as latest 
               FROM tbl_bankdata 
              GROUP BY lead_id) r 
         ON tbl_lead.c_id=r.lead_id  
      ORDER BY tbl_lead.date_of_created DESC;

Using the above code I am getting my correct output. I am getting each record. Now I have to set the where condition.

my issue is, I have a column called statusDone in the table tbl_bankdata. If the statusDone is 0 then display the records else not to display the records.

I tried

If I use statusDone=0 with the group by then I am getting all the records or If I use r.statusDone=0 then I am getting the error Unknown column 'r.statusDone' in 'where clause'

I am using Codeigniter. I just shared my SQL query.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
questionbank
  • 440
  • 8
  • 25
  • Subquery itself looks fishy: `select lead_id, b_bankDoneStatus, max(date_of_added) as latest from tbl_bankdata group by lead_id` `b_bankDoneStatus` is not wrapped by aggregate function and I doubt it is functionally dependent on lead_id column. Related: https://stackoverflow.com/a/33629201/5070879 – Lukasz Szozda Aug 11 '19 at 11:27
  • @LukaszSzozda, Can you help me with correct subquery? – questionbank Aug 11 '19 at 11:28
  • The first thing you should do is to provide http://dbfiddle.uk demo with sample data and desired result. Working on actual curated code is much easier than long description. – Lukasz Szozda Aug 11 '19 at 11:29
  • @LukaszSzozda, Yes I agree with your last comment. – questionbank Aug 11 '19 at 11:37

1 Answers1

1

I think you need to include statusDone column within the subquery(also within the GROUP BY list) :

SELECT * 
   FROM tbl_lead l
   LEFT JOIN tbl_documentsPickup dp ON l.c_id = dp.lead_id  
   LEFT JOIN (
              SELECT lead_id, statusDone, max(date_of_added) as latest 
                FROM tbl_bankdata 
               GROUP BY lead_id, statusDone     
              HAVING max(`date_of_added`) = latest              
              ) bd 
          ON l.c_id = bd.lead_id 
       WHERE bd.statusDone = 0
       ORDER BY l.date_of_created DESC;
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55