CODEWITHSUNDEEP
laravel
Krishna Shrestha
Krishna Shrestha

Reputation: 13

how to join two table only when foreign key value is not null

I want to join right (employees) table only when left (purchases) table's employee_id is NOT NULL but the query returning empty result set if the employee_id is NULL.

$data = DB::table('purchases')
            ->join('demands', 'purchases.demand_id', 'demands.id')
            ->join('fiscal_years', 'purchases.fiscal_year_id', 'fiscal_years.id')
            ->join('employees', function ($join){
                $join->on('purchases.employee_id', 'employees.id')
                    ->where('purchases.employee_id', '!=', null);
            })
            ->select('purchases.*', 'demands.code', 'fiscal_years.year', 'employees.name')
            ->get();

Upvotes: 1

Views: 545

Answers (1)

Sergey Bogdanov
Sergey Bogdanov

Reputation: 671

Try to use leftJoin. It will join employees if they are. 

$data = DB::table('purchases')
            ->join('demands', 'purchases.demand_id', 'demands.id')
            ->join('fiscal_years', 'purchases.fiscal_year_id', 'fiscal_years.id')
            ->leftJoin('employees', function ($join){
                $join->on('purchases.employee_id', 'employees.id')
                    ->where('purchases.employee_id', '!=', null);
            })
            ->select('purchases.*', 'demands.code', 'fiscal_years.year', 'employees.name')
            ->get();

Upvotes: 1

Related Questions