-1

I have two different tables in sql server database on the same host that I need to join together in a query.

I would like to use the laravel QueryBuilder to do so.

I've tried so far:

return DB::table('users')
    ->select([
        'users.id',
        'Resources.FirstName'
    ])
    ->join('Resources.dbo.ID', 'Resources.UserID', '=', 'users.id');

It results in the following error: General error: 1 near ".": syntax error (SQL: select "users"."id", "Resources"."FirstName" from "users" inner join "Resources"."dbo"."ID" on "Resources"."ID" = "users"."id")

If I copy the query in my dabatase script editor and run it, it runs correctly and give the expected result.

I have also tried this

return DB::table('users')
    ->select([
        'users.id',
        'Resources.FirstName'
    ])
    ->join(DB::raw('Resources.dbo.ID'), 'Resources.UserID', '=', 'users.id');

return DB::table('users')
    ->select([
        'users.id',
        'Resources.FirstName'
    ])
    ->join(DB::raw('Resources.ID'), 'Resources.UserID', '=', 'users.id');

->join('Resources', function($q) {
            $q->connection('tcollect')->on('Resources.ID', '=', 'users.id');
        });

Is there a way to achieve this?

naghal
  • 537
  • 3
  • 19

1 Answers1

1

Please try that

DB::table('Database1.Resources as dt1')-> join('Database2.users as dt2', 'dt2.id', '=', 'dt1.UserID')->select(['dt1.*','dt2.*'])->get();
Ahmet Firat Keler
  • 2,603
  • 2
  • 11
  • 22
  • I have tried this also, but it return the sql error `SQLSTATE[HY000]: General error: 1 no such table: Beta8.Resources (SQL: select "dt1"."id", "dt2"."id" from "Beta8"."Resources" as "dt1" inner join "ArmPay_staging"."users" as "dt2" on "dt2"."id" = "dt1"."UserID") ` evenf if the table does exists in the given database. – naghal Jun 27 '22 at 17:56
  • Is the second database on the same server? Do you have the necessary privileges to access it? – Ahmet Firat Keler Jun 27 '22 at 18:00
  • Yes I have access to both database, they are on the same server. I have found also that the query does work if it is run in tinker, but not if it is run from the test. Maybe it has something to do with the fact that the phpunit.xml file overides some env variable with the following? – naghal Jun 27 '22 at 18:05
  • 1
    Hey, can you check this out? https://stackoverflow.com/questions/44392039/laravel-multiple-databases-phpunit – Ahmet Firat Keler Jun 27 '22 at 18:44