Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

eternalchaos520's avatar

Multiple Database on Same Server Join Query

Hi, I had been searching for the solution in the internet for a long time but still I couldn't find the solution, most of the search results is joining on different tables in single database.

I need the solution for different DB::Connection left join and get the query results.

Please noted that I only want to view the results only, no any CRUD required.

Moreover, all databases are within the same server, if using single connection like DB::connection('sqlsrv') or ('sqlsrv2') can get the result easily only when join is having issues.

Below are my codes so far.

<?php

namespace App\Http\Controllers;

use App\UserInfo;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class UserInfoController extends Controller
{
    public function index() 
    {       
        // $UserInfos = DB::connection('sqlsrv2')->select('select * from TMS_CLOCKING');                            
        // $userinfos = DB::connection('sqlsrv')->select('select * from userinfo');

        // $userinfos = DB::table('USERINFO')->where('USERID', '=', '252')->get();      
        // $userinfos = DB::table('CHECKINOUT')
        //          ->leftJoin('USERINFO', 'CHECKINOUT.USERID', '=', 'USERINFO.USERID')
        //          ->where('USERINFO.CardNo', '=', '1411078')
        //          ->get();


        // $db = DB::connection('sqlsrv2');

        $employee_profile = 
                    DB::connection('sqlsrv2')
                    ->table('EMPLOYEE')
                    ->where('ISACTIVE', '=', 'Y')
                    ->where('CARDID', '!=', '')
                    ->where('DEPARTMENT', '!=', '')
                    ->get();

        // $employee_attendance =
        //          DB::table('CHECKINOUT')
        //          ->leftJoin('USERINFO', 'CHECKINOUT.USERID', '=', 'USERINFO.USERID')
        //          ->leftJoin('TMS_EMPLOYEE', 'USERINFO.CardNo', '=', 'TMS_EMPLOYEE.CardID')
        //          ->get();

        // $db = DB::Connection('sqlsrv2')->('EMPLOYEE');
        // $employee_attendance =
        //          DB::table('CHECKINOUT')
        //          ->leftJoin('USERINFO', 'CHECKINOUT.USERID', '=', 'USERINFO.USERID')
        //          ->leftjoin($db, 'USERINFO.CardNo', '=', 'EMPLOYEE.CARDID')
        //          ->get();

        $employee_attendance =
                    DB::Connection('sqlsrv')
                    ->table('CHECKINOUT')
                    ->leftJoin('USERINFO', 'CHECKINOUT.USERID', '=', 'USERINFO.USERID')
                    ->leftjoin(DB::Connection('sqlsrv2'))                       
                        ->table('EMPLOYEE', 'USERINFO.CardNo', '=', 'EMPLOYEE.CardID')
                    ->get();

        $employee_attendance = 
                    DB::table(DB::raw('EMPLOYEE'))

        // $userinfos = $employee_profile;
        $userinfos = $employee_attendance;

        return view('userinfo.index', ['userinfos' => $userinfos]);     
    }
}
0 likes
7 replies
Vilfago's avatar

It will be easier using model.

Create in your config file a connection for each database you have. (https://laravel.com/docs/5.7/database#using-multiple-database-connections)

Create a Model for each table (https://laravel.com/docs/5.7/eloquent#defining-models), in specifying on which connection it is

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Flight extends Model
{
    /**
     * The connection name for the model.
     *
     * @var string
     */
    protected $connection = 'connection-name';
}

And then set relations between these models (https://laravel.com/docs/5.7/eloquent-relationships).

And query what you want :)

eternalchaos520's avatar

@VILFAGO - Erm, I am not intend to create the model to manipulate the data, I just want to extract data from database only.

Vilfago's avatar

Yes, but the only way you can do it, is how Eloquent do it in the back.

  • Get the data from one DB
  • check all the foreign key you need to get in the 2nd DB
  • get these data from 2nd DB
  • combine all of these data using key and foreign key

You can code it on your own, or use Eloquent to do it.

eternalchaos520's avatar

@VILFAGO - I had tried to code it my own but failed, that's why I open a thread here. As I tested so far, the default db can be connected without using DB:connection, however the second db is required to use DB:connection in order to connect it.

Is there anyway to connect second database with left join at the same time?

                   DB::Connection('sqlsrv')
                    ->table('CHECKINOUT')
                    ->leftJoin('USERINFO', 'CHECKINOUT.USERID', '=', 'USERINFO.USERID')
                    ->leftjoin(DB::Connection('sqlsrv2'))                       
                        ->table('EMPLOYEE', 'USERINFO.CardNo', '=', 'EMPLOYEE.CardID')
                    ->get();

If there is the solution can resolve connection part then I think my problem can be solved.

jonnywilliamson's avatar

@eternalchaos520

In the insane chance you ever read this and remember, did you ever find a way to change the database connection for a join between two seperate databases like you were trying to do?

I would love to be able to do the same!

eternalchaos520's avatar
eternalchaos520
OP
7 years ago
Best Answer
Level 2

in the end I used sql view to join the query first then using query builder to get the data.

Please or to participate in this conversation.