Query type JOIN with 2 SQL server databases?

Question

Query type JOIN with 2 SQL server databases?

Navigation

#1 by (0 votes)

0

I'm doing a comparison between 2 tables of the same name with PHP from a database in SQL SERVER

 <?php 

    $sql = "SELECT ITM1.ItemCode, OITM.ItemCode, OITM.ItemName, ITM1.Price, ITM1.PriceList FROM OITM, ITM1 WHERE ITM1.ItemCode = OITM.ItemCode AND ITM1.PriceList=1 ORDER BY ITM1.ItemCode" ;
    $sql2 = "SELECT ITM1.ItemCode, .OITM.ItemCode, OITM.ItemName, ITM1.Price, ITM1.PriceList FROM OITM, ITM1 WHERE ITM1.ItemCode = OITM.ItemCode AND ITM1.PriceList=1 ORDER BY ITM1.ItemCode ";
    $stmt = sqlsrv_query( $conn_sisT, $sql );
    $stmt2 = sqlsrv_query( $conn_sisM, $sql2 );

       if( $stmt === false) {
        die( print_r( sqlsrv_errors(), true) );
        }
       if( $stmt2 === false) {
     die( print_r( sqlsrv_errors(), true) );
            }

        while ( $rowT = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
              while ($rowM = sqlsrv_fetch_array( $stmt2, SQLSRV_FETCH_ASSOC) ) {
                $itemcode = $rowM['ItemCode'];

                 if (array_search($itemcode,$rowT)) {//son iguales

                 if ($rowT['Price']==$rowM['Price']) {//mismo precio
                        $p=$rowT['Price']+0;
                        echo "<tr><td>".$rowT['ItemCode']."  </td><td> ".$rowT['ItemName']." </td><td> ".$p." MXN </td>   </tr>";
                        break;  
}
if ($rowT['Price']!=$rowM['Price']) {

$p=$rowT['Price']+0;
echo "<tr><td>".$rowT['ItemCode']."  </td><td> ".$rowT['ItemName']." </td><td style='color:red;'> ".$p." MXN MASTER: ".$rowM['Price']."</td></tr>";
break; 
    }
 }
else {
$p=$rowT['Price']+0;
$m=$rowM['Price']+0;
echo "<tr><td>".$rowT['ItemCode']." | ".$rowM['ItemCode']."  
</td><td> ".$rowT['ItemName']." | ".$rowM['ItemName']." 
</td><td> ".$p." MXN | ".$m." MXN</td>  </tr>";
break; 
 }

}
}

?>

The problem is that it compares linearly, I need to do an INNER JOIN but the tables are found in different databases. I already tried with DBname.dbo.tablename but it does not give me any results.

sql php sql-server

asked by Alfredo Gutierrez Cendejas 06.02.2018 в 19:56

source

1 answer

Paint a table with php Browse recursive array

score 0 · Answer 1

I did not give any errors, comment on this code and implement the other one, I just need the prices to be different and if it does not exist it is printed. they are the same server, username and password. just different databases.

$sql = "SELECT SBO_TRANSMISIONES.dbo.ITM1.ItemCode, SBO_TRANSMISIONES.dbo.OITM.ItemCode, SBO_TRANSMISIONES.dbo.OITM.ItemName, SBO_TRANSMISIONES.dbo.ITM1.Price, SBO_TRANSMISIONES.dbo.ITM1.PriceList, SBO_MASTERS.dbo.ITM1.Price,SBO_MASTERS.dbo.ITM1.ItemCode
								
FROM SBO_TRANSMISIONES.dbo.OITM, SBO_TRANSMISIONES.dbo.ITM1, SBO_MASTERS.dbo.OITM, SBO_MASTERS.dbo.ITM1 

WHERE SBO_TRANSMISIONES.dbo.ITM1.ItemCode = SBO_TRANSMISIONES.dbo.OITM.ItemCode 
AND SBO_MASTERS.dbo.OITM.ItemCode = SBO_TRANSMISIONES.dbo.ITM1.ItemCode 
AND SBO_TRANSMISIONES.dbo.ITM1.PriceList=1 

ORDER BY SBO_TRANSMISIONES.dbo.ITM1.ItemCode" ;

I put it directly so that it is understood where each data is from.