CODEWITHSUNDEEP
sql-serverjoinsqlsrv
Putty
Putty

Reputation: 37

MSSQL - Join tables, in different databases, on the same server

I have 2 tables, in different databases, on the same server. I would like to output the differences.

Database 1: dbo.employees
Table: employees

id        name       
---       ---- 
1          John  
2          Ringo
3          Paul
4          George


Database 2: dbo.timecard
Table: time

timeid    timename       
---       ---- 
1          John  
2          Ringo
3          Paul

Desired output:

resultid  resultname       
  ---       ---- 
   4       George

I've set up the connection info:

<?PHP   
$serverName = "server";
$UID = "dbuser";
$PWD = "dbpass";
$Database1 = "employees";
$Database2 = "timecard";

$connectionOptions1 = array("Database"=>$Database1, "UID"=>$UID, "PWD"=>$PWD);
$connectionOptions2 = array("Database"=>$Database2, "UID"=>$UID, "PWD"=>$PWD);

$conn1 = sqlsrv_connect( $serverName, $connectionOptions1);
$conn2 = sqlsrv_connect( $serverName, $connectionOptions2);

if( $conn === false ) 
var_dump(sqlsrv_errors());
?>

And created 2 queries:

<?PHP
$EmployeeSelect = sqlsrv_query($conn1,"SELECT * FROM dbo.employees ORDER BY name");
 WHILE ($EmployeeFetch=sqlsrv_fetch_array($EmployeeSelect, SQLSRV_FETCH_ASSOC)){
ECHO $EmployeeFetch['name']." / ".$EmployeeFetch['id'];
ECHO "<BR>";
 }
?>

<?PHP
$TimeSelect = sqlsrv_query($conn2,"SELECT * FROM dbo.timecard ORDER BY timename");
 WHILE ($TimeFetch=sqlsrv_fetch_array($TimeSelect, SQLSRV_FETCH_ASSOC)){
ECHO $TimeFetch['timename']." / ".$TimeFetch['timeid'];
ECHO "<BR>";
 }
?>

How does one deal with 2 different connection strings in a single query?

Upvotes: 1

Views: 1862

Answers (3)

Anon
Anon

Reputation: 10908

SELECT id,name FROM db1.dbo.Employee
EXCEPT
SELECT timeid,timename FROM db2.dbo.Time

Upvotes: 0

Dave C
Dave C

Reputation: 7392

I would run it as a single SQL statement instead:

SELECT E.*
FROM db1.dbo.Employee E
LEFT OUTER JOIN db2.dbo.Time T
   ON E.name = T.timename
WHERE T.timename IS NULL

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33571

You don't need multiple connection strings since these are on the same server. Your connection will have a default database of whatever. Let's say your second query needs to pull data from another database than the default. You just need to use 3 part naming.

As a side note, you should always explicitly name your columns instead of using *.

Select * 
from MySecondDatabase.dbo.timecard

Upvotes: 1

Related Questions