Get all value including null value

Question

In my coordinator_status column, there are values and null. When I run this code it only returns the ones with value only. I want to get null values too but I don't know how.

$coordetails = DB::table('courselist')
                        ->join('users','users.id','=','courselist.coordinator')
                        ->join('status','status.id','=','courselist.coordinator_status')
                        ->select('courselist.id','users.name','status.status')
                        ->where('courselist.faculty_id','=',$facultyid)
                        ->get();

This isn't a Laravel problem, it is a basic SQL problem (which has been resolved many times on Stack Overflow over the years). — mickmackusa, Nov 22 '21 at 06:55

score 1 · Accepted Answer · answered Nov 22 '21 at 05:35

to do this, you should use leftJoin:

$coordetails = DB::table('courselist')
                        ->leftJoin('users','users.id','=','courselist.coordinator')
                        ->leftJoin('status','status.id','=','courselist.coordinator_status')
                        ->select('courselist.id','users.name','status.status')
                        ->where('courselist.faculty_id','=',$facultyid)
                        ->get();

The LEFT JOIN keyword returns all records from the left table , and the matching records from the right table if there are any.

score 0 · Answer 2 · answered Nov 22 '21 at 06:26

according to me if you want to get null and value both data you have to use left join instead of join

you can find documentation on laravel site join in laravel

if you want to get courselist with joined user

DB::table('courselist')
->select('courselist.id','users.name','status.status')
->join('users','users.id','=','courselist.coordinator')
->leftJoin('status','status.id','=','courselist.coordinator_status')
->where('courselist.faculty_id','=',$facultyid)
->get();

if you want to get all courselist data without user relation then

DB::table('courselist')
->leftJoin('users','users.id','=','courselist.coordinator')
->leftJoin('status','status.id','=','courselist.coordinator_status')
->select('courselist.id','users.name','status.status')
->where('courselist.faculty_id','=',$facultyid)
->get();

and sql query will be like

SELECT * FROM testDb.course LEFT JOIN testDb.status ON testDb.course.status=testDb.status.id;

Wasn't this advice already given nearly an hour earlier? – mickmackusa Nov 22 '21 at 06:53 — mickmackusa, Nov 22 '21 at 06:53

Get all value including null value

2 Answers2