How to get value from other table to integrate into subquery?

I had table as follows:

 ID | NegeriID  | yeartamatkhidmat | yeartamatkhidmat
-----------------------------------------------------------------
 1  |     2     |      2001        |      2002
 1  |     2     |      2003        |      2007
 2  |     2     |      2008        |      2012
 2  |     1     |      2013        |      2018
 3  |     3     |      2000        |      2001

I had a subquery to get the sum of years of working experience according to Negeri(state) as follows:

 $query = DB::table('itemregistrations')
                ->join('sections', 'itemregistrations.SectionID', '=', 'sections.SectionID')
                ->join('categories', 'itemregistrations.CategoryID', '=', 'categories.CategoryID')
                ->join('operasi', 'itemregistrations.OperasiID', '=', 'operasi.OperasiID')
                ->join('negeri', 'itemregistrations.NegeriID', '=', 'negeri.NegeriID')
                ->join('gred', 'itemregistrations.GredID', '=', 'gred.GredID')
                ->where('itemregistrations.statusProID', '=', 1)
                ->select('itemregistrations.name','sections.sectionname', 'categories.categoryname', 'operasi.operasiname', 'itemregistrations.Nobadan', 'itemregistrations.lahir_yy', 'itemregistrations.pdrm_yy', 'gred.namagred', 'itemregistrations.itemRegistrationID', '');


 $query->select(DB::raw('m.itemRegistrationID, m.NegeriID, sum(distinct m.duration)'))
                  ->from(DB::raw('(SELECT itemRegistrationID, NegeriID, yeartamatkhidmat - yearmulakhidmat as duration FROM itemregistrationpangkat) AS m 
                        RIGHT JOIN itemregistrations ON itemregistrations.itemRegistrationID=m.itemRegistrationID'))
                  ->groupBy('m.itemRegistrationID', 'm.NegeriID') //must be used together with sum()
                  ->havingRaw('sum(m.duration) >= ?', [request('tempoh_negeri')]);

I had another table with the value of name and Negeri:

    ID |  Name |
    --------------
    1  |   A   | 
    2  |   B   | 
    3  |   C   |  


   ID |  Negeri 
    --------------
    1  | Bill  
    2  | Anne   
    3  | June3 

How to take value of name and Negeri from the tables to integrate into the subquery above?

from Newest questions tagged laravel-5 - Stack Overflow http://bit.ly/2VD62vf
via IFTTT