UNION troubles !

[swatisonee]

Here's what i've been attempting to do .

 

a. I have a table "travelexpense" in 2 DBs. All columns are identical.

b. Based on a set of parameters, a user can have a record in this table in one or both of the databases.

c. When i want to output all the entries pertaining to that user in both these tables, I use Select Union. So far so good.

d. However, the query references customers pertaining to those expenses . The customer table is present in both databases but is populated with different records.

e. Now when the SELECT UNION runs, it picks up only the customer in the first database and not the second, even if there is an entry pertaining only to the second database.

f. Odlly though the other data - that is the expenses themselves are selected correctly. Its only the customers entries that are wrong

 

Code snippet below

 

$sqlZ= " SELECT * FROM $dbname2.`Travelexpense`  WHERE Userid='$user' && MONTH(`Enddate`)=$month && YEAR(`Enddate`)=$year  
UNION 

select * from $dbname3.`Travelexpense`  WHERE Userid='$user' && MONTH(`Enddate`)=$month && YEAR(`Enddate`)=$year  ";

$resultZ = mysql_query($sqlZ) 	or die (mysql_error());

$numofrows = mysql_num_rows($resultZ);

for($i = 0; $i < $numofrows; $i++) {

$myrowZ = mysql_fetch_array($resultZ);
$coid = $myrowZ["CID"];

$s1 = "SELECT * FROM $dbname2.`Customers` WHERE `CID` = $coid  UNION select * from $dbname3.`Customers` WHERE `CID` = $coid";
$result1 = mysql_query($s1);
$myrow2 = mysql_fetch_array($result1);
$Name=$myrow2["Company"];

//here's the problem . If say $coid = 150 , then it will look only at record 150 in Table Customers in dbname2 and NOT in Table Customers in dbname3 even if only dbname3 has a relevant entry.

blah blah 
}

 

Am i using UNION incorrectly  esp in the query $s1?

 

Appreciate any help . Thanks !

Swati

[sunfighter]

This may not be an answer but surely something helpful. I use UNION ALL, it's faster than UNION. The down side: ALL does just that shows everything even if it's a duplicate file, while just UNION will take the time (why it's slower) to look for and delete dups. So I start with ALL and if I don't like the results I remove it.

[swatisonee]

silly mistake ! this worked .

$s1 = "select * from $dbname2.`Travelexpense`  WHERE Userid='$user' && MONTH(`Enddate`)=$month && YEAR(`Enddate`)=$year  AND `CID` = '$coid' AND `RID` = '$roid'";
blah blah