...

View Full Version : Problem encountered in select statement from two databases



newphpcoder
12-06-2011, 02:49 AM
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...

sunfighter
12-06-2011, 08:37 PM
Try this for your query:


$query = "
select * from hris
inner join payroll on payroll.emp_no = hris.emp_id
inner join wage on wage.emp_id = hris.emp_id";
$results = mysql_query($query);

Old Pedant
12-06-2011, 08:43 PM
When you use an "implicit join" (that is, a join without the keyword JOIN), you are *always* doing an INNER JOIN. No other choice.

If you want to always get records from one table even if there are no matching records in some other table, you must use an *explicit* OUTER JOIN. That is, either a LEFT JOIN or a RIGHT JOIN.

*possibly* what you are after:


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'
GROUP BY c.EMP_NO

Except that GROUP MAKES no sense. You don't have any aggregate function in there (e.g, MIN(), MAX(), SUM(), etc.) so the GROUP BY won't do anything. Almost surely what you need is ORDER BY, instead.

EDIT: If you know that every employment record indeed has a matching wage record, then you might want to change the first LEFT JOIN to an INNER JOIN. But the second join surely needs to be a LEFT JOIN.

Old Pedant
12-06-2011, 08:48 PM
And Sunfighter: Your query is the exact equivalent of his original. The syntax is different, but the semantics are 100% the same. Well, it would be, except that he doesn't seem to have a table named hris and also he seems to be getting data from two different databases which your query ignored.

sunfighter
12-07-2011, 05:56 AM
@Old Pedant, please explain this to me. I don't know where hris came from it should have been employment. But I did make a db with those tables and the columns he gave and did run my query and did get ll the info.

I'll look at his and my query to see why they are the same. But you said" he seems to be getting data from two different databases which your query ignored. " I am in the dark here I thought I was getting from the tables he wanted.

And could you point me to some site to learn joins from (not mysql site, had a hard time with that one). Please.

Old Pedant
12-07-2011, 06:24 AM
You'd have to see his full PHP code to know that actual names of his two databases, but look here (his original code made readable):


$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";

He has two PHP variables, $ADODB_DB and $PAYROLL, and those contain the names of his two databases. Let's say he has prior PHP code that does

$ADODB_DB = "empinfo";
$PAYROLL = "pay";


So that means his query is really doing


$sql = "SELECT em.EMP_NO, em.STATUS, w.RATE, c.Casual_Hours "
. " FROM empinfo.wage w, empinfo.employment em, pay.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";

Yes, in MySQL it is perfectly legal to use data from as many databases as you wish, so long as they are all on the same server. You simply use the database name as a prefix to the table name. (There's also a way to use a database on another server, but let's not get into that.)

And yes, both your query and his will work fine, *SO LONG* as there is indeed data in the Casual_Hours table for EACH AND EVERY employee.

But now try running your query after adding a new employee to the employment table but not to the wage table or Casual_Hours table.

That employee won't show up in your results.

Go ahead and add a record in the wage table for that new employee. Again run the query. The new employee still won't show up.

Finally add a record for that employee to the Casual_Hours table and run your query. *FINALLY* the employee shows up.

Run the same experiment with my query and the employee always shows up, with NULL data in the fields for the other tables if there is no match in those tables.

As phpcoder said:
i want to displayed rate even he has no hours

And *THAT* is indeed how outer joins work.

Joins work the same way in all databases, so you could start here:
http://www.sql-tutorial.net/

Old Pedant
12-07-2011, 06:28 AM
PHPCoder: I missed this part of your first post:


i want to displayed rate even he has no hours, and hours will be 00:00

You've probably discovered by now that you just get NULL back for the hours if there are none. You could of course take care of that in your PHP code, but...

Easy fix.


SELECT em.EMP_NO, em.STATUS, w.RATE, IFNULL(c.Casual_Hours,'0:00:00') AS `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'
ORDER BY em.EMP_NO, em.STATUS, Casual_Hours

As I said, your GROUP BY is bogus, so I changed it to what I *guess* would be the right ORDER BY.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum