varun varun - 4 months ago 9
SQL Question

MySQL - I want my table JOIN to return a single row based on a condition

in the below code there are multiple entries in 'leads' table with the same 'account_id'. I want it to return a single row - the one with the minimal value of another field 'date_entered'. I cannot use 'group by' on account_id as I intend to use 'group by' on BU and get summation accordingly. Please help.

select uc.business_unit_dp_c,
FORMAT(SUM(CASE
WHEN lc.source_leads_c not in ('Discovery','Discovery SuperEmail','Self Generated','Partner','Channel_Partner') and k.id<>'' THEN k.order_value
WHEN lc.source_leads_c not in ('Discovery','Discovery SuperEmail','Self Generated','Partner','Channel_Partner') and s.id<>'' THEN s.sivr_aiv_inr
ELSE 0
END),0)
as Online,
FORMAT(SUM(CASE
WHEN lc.source_leads_c in ('Discovery', 'Discovery SuperEmail') and k.id<>'' THEN k.order_value
WHEN lc.source_leads_c in ('Discovery', 'Discovery SuperEmail') and s.id<>'' THEN s.sivr_aiv_inr
ELSE 0
END),0)
as Discovery,
FORMAT(SUM(CASE
WHEN lc.source_leads_c in ('Partner','Channel_Partner') and k.id<>'' THEN k.order_value
WHEN lc.source_leads_c in ('Partner','Channel_Partner') and s.id<>'' THEN s.sivr_aiv_inr
ELSE 0
END),0)
as Self_Generated_CP
from opportunities as o
left join opportunities_cstm as oc on o.id=oc.id_c
left join opportunities_knw_caf_1_c as ok on o.id=ok.opportunities_knw_caf_1opportunities_ida
left join knw_caf as k on ok.opportunities_knw_caf_1knw_caf_idb=k.id
left join opportunities_knw_sivr_caf_1_c as os on os.opportunities_knw_sivr_caf_1opportunities_ida=o.id
left join knw_sivr_caf as s on s.id=os.opportunities_knw_sivr_caf_1knw_sivr_caf_idb
left join accounts_opportunities as ao on ao.opportunity_id=o.id
left join leads as l on l.account_id=ao.account_id and l.account_id <> ''
left join leads_cstm as lc on lc.id_c=l.id
left join users_cstm as uc on uc.id_c=o.assigned_user_id
where o.sales_stage='clw' and
(k.id<>'' or s.id<>'') and o.jira_raise_date <> '' and
(o.tranjection_type in ('Fresh Plan / New Customer','Number Activation','Revival','Balance Amount') or o.transaction_sivr in ('Paid Project','Number Allocation','New Feature')) and
o.jira_raise_date between '2016-06-01' and curdate()
group by uc.business_unit_dp_c

Answer

Write SQL just as you described

 Select * 
 from from opportunities o
     left join opportunities_cstm oc 
        on o.id = oc.id_c
     left join opportunities_knw_caf_1_c ok 
        on o.id = ok.opportunities_knw_caf_1opportunities_ida
     left join knw_caf k 
        on ok.opportunities_knw_caf_1knw_caf_idb = k.id
     left join opportunities_knw_sivr_caf_1_c os 
        on os.opportunities_knw_sivr_caf_1opportunities_ida=o.id
     left join knw_sivr_caf s 
        on s.id = os.opportunities_knw_sivr_caf_1knw_sivr_caf_idb
     left join accounts_opportunities ao 
        on ao.opportunity_id=o.id
     left join leads l 
        on l.account_id=ao.account_id 
           and l.account_id <> ''
     left join leads_cstm lc 
        on lc.id_c = l.id
     left join users_cstm uc 
        on uc.id_c = o.assigned_user_id
where o.sales_stage = 'clw' and
   and (k.id <> '' or s.id <> '') 
   and o.jira_raise_date <> '' 
   and (o.tranjection_type in 
        ('Fresh Plan / New Customer',
         'Number Activation','Revival','Balance  Amount') or
       o.transaction_sivr in 
        ('Paid Project','Number Allocation','New Feature')) 
   and o.jira_raise_date between '2016-06-01' and curdate()

-- next, add this additional predicate to Where clause... use table w/DateEntered column

   and date_entered =
       (Select Min(date_entered) 
        From accounts_opportunities os
            join tableWithDateEntered dr -- Table w/DateEntered
               on ?????                  -- proper join criteria here
        Where os.account_id = l.account_id)

--- or as constructed by op ( and simplified by me, since both account_id and date_entered are in table leads, that's the only table that needs to be referenced in the subquery).....

   and l.date_entered = 
      (select min(date_entered) 
       from leads
       where account_id = l.account_id)