[FIXED] Selecting from multiple tables in laravel

Issue

please help.I have a test booking table that looks like this

Schema::create('test_bookings', function (Blueprint $table) {
        $table->unsignedInteger('RequestID');
        $table->string('bookingDate');
        $table->string('timeSlot');
        $table->unsignedInteger('nurse_id');
        $table->timestamps();
    });

and a tests table that looks like this

Schema::create('tests', function (Blueprint $table) {
        $table->unsignedInteger('RequestID');
        $table->unsignedInteger('patientID');
        $table->string('barcode');
        $table->string('temperature');
        $table->string('pressure');
        $table->string('oxygen');
        $table->unsignedInteger('nurseID');
        $table->timestamps();
    });

I want to show the RequestID,bookingDate,timeSlot, name and surname of the nurse only if the test_bookings RequestID is in tests table. This is my nurse table

Schema::create('nurses', function (Blueprint $table) {
        $table->unsignedInteger('nurseID');
        $table->string('name');
        $table->string('surname');
        $table->string('idNumber');
        $table->string('phone');
        $table->string('email');
        $table->unsignedInteger('suburb_id');
        $table->timestamps();


        $table->index('suburb_id');
    });

This is the code that i tried

$tests =  DB::table('tests')
                ->select('RequestID','bookingDate','timeSlot','name','surname')
                ->join('nurses','nurses.nurseID','test_bookings.nurse_id')
                ->join('test_bookings','test_bookings.RequestID','=','tests.RequestID')
                ->get();

Solution

but when I join the tests table nothing is showing

that because you are using join clause that generate innerJoin statement, and to see the results you should use leftJoin

$tests =  DB::table('tests')
                ->select('RequestID','bookingDate','timeSlot','name','surname')
                ->leftJoin('nurses','nurses.nurseID','=','test_bookings.nurse_id')
                ->leftJoin('test_bookings','test_bookings.RequestID','=','tests.RequestID')
                ->get();

Answered By - OMR