Problem encountered in select statement from two databases

Hi

I have select statement to get the Rate and Hours of employee, and I had noticed that if no hours save in database for that employee but he has rate, the rate was not also displayed. i want to displayed rate even he has no hours, and hours will be 00:00

the fields is came from those databases and tables.

HRIS - database name

Tables and fields list

employment AS em
EMP_ID
EMP_NO
STATUS

wage AS w
EMP_ID
RATE

payroll - Database Name

Table Name and Fields
casual_hours As c
EMP_NO
Casual_Hours

Here is my code:


if($STATUS == 'Casual'){
 
  $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em, $PAYROLL.casual_hours c WHERE em.EMP_NO = c.EMP_NO AND w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' GROUP BY c.EMP_NO"; 
  $RsEarnings = $conn2->Execute($sql); 
  
  $Rate      = $RsEarnings->fields['RATE'];
  $Hours      = $RsEarnings->fields['Casual_Hours'];

  $Hours = substr($Hours, 0, 5);
  $Hours = str_replace(':', '.', $Hours);
  
 $Amount = $_POST["Amount"];
 
 $Amount = round(($Hours/8)* $Rate, 2);  
 }
 else{
     $Hours = ('00:00');
     $Amount = (0);
 }
  
  $smarty->assign('Rate', $Rate);
  $smarty->assign('Hours', $Hours);
  $smarty->assign('Amount', $Amount);

Thank you so much…

use a LEFT OUTER JOIN

can you give an example im not familiar in left outer join…

Thank you

is it like this?


SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
  LEFT JOIN table3 ON table2.id=table3.id;

Thank you

i revised my code…

here is my new code:


<?php
if($STATUS == 'Casual'){
    
  $sql = "SELECT em.EMP_NO, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w LEFT JOIN $ADODB_DB.employment em ON w.EMP_ID=em.EMP_ID
  LEFT JOIN $PAYROLL.casual_hours c ON em.EMP_NO=c.EMP_NO";
  $RsEarnings = $conn2->Execute($sql); 
  
  $Rate      = $RsEarnings->fields['RATE'];
  $Hours      = $RsEarnings->fields['Casual_Hours'];

  $Hours = substr($Hours, 0, 5);
  $Hours = str_replace(':', '.', $Hours);
  
 $Amount = $_POST["Amount"];
 
 $Amount = round(($Hours/8)* $Rate, 2);  
 }
 else{
     $Hours = ('00:00');
     $Amount = (0);
 }
?>

it works…i want to know how can i add 00.00 if no data from hours…now when no hours the textbox become blank,or empty i want it to have 00.00…

Thank you

use COALESCE

you’ve never taken any SQL training, have you

i think it is time that you do

:slight_smile:

Ok i will search about use COALESCE

Thank you

When I tried this query in mysql


SELECT em.EMP_NO, w.RATE, r.Hours FROM hris.wage w LEFT JOIN hris.employment em ON w.EMP_ID=em.EMP_ID LEFT JOIN payroll.regular_sum_hours r ON em.EMP_NO = r.EMP_NO; 

but when i put to php with this code:


$sql = "SELECT EMP_ID,EMP_NO, STATUS FROM employment WHERE EMP_ID = '$currentEmpID'";
 $rsStatus = $conn->Execute($sql);
 
 $STATUS = $rsStatus->fields['STATUS'];
 $EMP_ID = $rsStatus->fields['EMP_ID'];
 
 if ($STATUS == 'Regular'){
 
 //$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em, $PAYROLL.regular_sum_hours r WHERE em.EMP_NO = r.EMP_NO AND w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' GROUP BY r.EMP_NO"; 
   $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours FROM $ADODB_DB.wage w LEFT JOIN $ADODB_DB.employment em ON w.EMP_ID = em.EMP_ID 
   LEFT JOIN $PAYROLL.regular_sum_hours r ON em.EMP_NO = r.EMP_NO";    
  $RsEarnings = $conn2->Execute($sql); 
  
  $Rate      = $RsEarnings->fields['RATE'];
  $Hours      = $RsEarnings->fields['Hours'];

  $Hours = substr($Hours, 0, 5);
  $Hours = str_replace(':', '.', $Hours);
  
 $Amount = $_POST["Amount"];
 
 $Amount = round(($Hours/8)* $Rate, 2); 
 }
 elseif($STATUS == 'Casual'){
 
  //$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w, $ADODB_DB.employment em, $PAYROLL.casual_hours c WHERE em.EMP_NO = c.EMP_NO AND w.EMP_ID = '$currentEmpID' AND em.EMP_ID = '$currentEmpID' GROUP BY c.EMP_NO"; 
  $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours FROM $ADODB_DB.wage w LEFT JOIN $ADODB_DB.employment em ON w.EMP_ID=em.EMP_ID
  LEFT JOIN $PAYROLL.casual_hours c ON em.EMP_NO=c.EMP_NO";
  $RsEarnings = $conn2->Execute($sql); 
  
  $Rate      = $RsEarnings->fields['RATE'];
  $Hours      = $RsEarnings->fields['Casual_Hours'];

  $Hours = substr($Hours, 0, 5);
  $Hours = str_replace(':', '.', $Hours);
  
 $Amount = $_POST["Amount"];
 
 $Amount = round(($Hours/8)* $Rate, 2);  
 }
 else{
     $Hours = ('00:00');
     $Amount = (0);
 }
  
  $smarty->assign('Rate', $Rate);
  $smarty->assign('Hours', $Hours);
  $smarty->assign('Amount', $Amount);

when the employee is Regular. the Rate and Hours are all the same, they only get the first row in the database.but whenI run it to mysql it works i only change the $ADODB_DB to hris and $PAYROLL to payroll

Thank you so much…

sounds like a php programming issue

I see no loop in the php code? You only assign the values once.

And by the way, I would change the queries like this (see the red line, I edited the “casual” as example):


SELECT 
    [B][COLOR="#FF0000"]w.EMP_NO[/COLOR][/B]
  , em.STATUS
  , w.RATE
  , c.Casual_Hours 
FROM $ADODB_DB.wage w 
LEFT JOIN $ADODB_DB.employment em 
ON w.EMP_ID=em.EMP_ID
LEFT JOIN $PAYROLL.casual_hours c 
ON em.EMP_NO=c.EMP_NO

If you take emp_no from a left joined table, it will be NULL if no row exists in that table. Taking it from the primary table in the query, it will always have a value.

But no EMP_NO in wage table…only EMP_ID

Thank you

I tried this code…suggested by other programmer:


$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours 
FROM $ADODB_DB.employment AS em 
LEFT JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
LEFT JOIN $PAYROLL.casual_hours AS c ON em.EMP_NO = c.EMP_NO
WHERE  em.EMP_ID = '$currentEmpID'";

Thank you

O yeah, my mistake.
Makes me wonder why you have EMP_NO and EMP_ID though. Isn’t EMP_NO a unique number that identifies the employee?

And does it work?

Yes, but I revised it:


 $sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, r.Hours 
FROM $ADODB_DB.employment AS em 
INNER JOIN $ADODB_DB.wage AS w ON em.EMP_ID = w.EMP_ID
LEFT JOIN $PAYROLL.regular_sum_hours AS r ON em.EMP_NO = r.EMP_NO
WHERE  em.EMP_ID = '$currentEmpID'";