I am still new to this, but I turned the manual upside down, or at least I
tried.
I am designing a payroll application by which payslips can be processed for
each employee for each period.
The problem lays where I would like to display a list of employees that
don't have transactions (payslips) for a particular period. I can easily
display a list of employees that do have transactions (payslips) for a
particular period. I tried reversing this, but it doesn't seem to work, or
I'm missing something somewhere. Please help.
# Table structure for table `employees`
emp_idemp_numemp_titleemp_surnameemp_nameemp_initials
emp_termdate // etc.
INSERT INTO `employees` VALUES (1, 'EMP001', 'Mrs.', 'Surname1', 'Name1',
'N1.', '-00-00');
INSERT INTO `employees` VALUES (2, 'EMP002', 'Mr.', 'Surname2', 'Name2',
'N2.', '-00-00');
INSERT INTO `employees` VALUES (3, 'EMP003', 'Mr', 'Surname3', 'Name3',
'N3.', '-00-00');
INSERT INTO `employees` VALUES (4, 'EMP004', 'Mr.', 'Surname4', 'Name4',
'N4.', '2003-08-31');
INSERT INTO `employees` VALUES (5, 'EMP005', 'Mr.', 'Surname5', 'Name5',
'N5.', '2004-02-28');
INSERT INTO `employees` VALUES (6, 'EMP006', 'Mr.', 'Surname6', 'Name6',
'N6.', '-00-00');
INSERT INTO `employees` VALUES (7, 'EMP007', 'Mr.', 'Surname7', 'Name7',
'N7.', '-00-00');
# Table structure for table `payperiods`
pp_idpp_periodpp_status
INSERT INTO `payperiods` VALUES (1, 'JAN 2004', 'inactive');
INSERT INTO `payperiods` VALUES (2, 'FEB 2004', 'active');
# Table structure for table `transactions`
CREATE TABLE `transactions` (
`tran_id` int(11) NOT NULL auto_increment,
`pp_id` int(11) NOT NULL default '0',
`emp_id` int(11) NOT NULL default '0',
`tran_basicsal` decimal(11,2) NOT NULL default '0.00',
`tran_OT_rate` decimal(11,2) NOT NULL default '0.00',
`tran_OT_qty` decimal(11,2) NOT NULL default '0.00',
`tran_DT_rate` decimal(11,2) NOT NULL default '0.00',
`tran_DT_qty` decimal(11,2) NOT NULL default '0.00',
`tran_bonus` decimal(11,2) NOT NULL default '0.00',
`tran_commission` decimal(11,2) NOT NULL default '0.00',
`tran_travelall` decimal(11,2) NOT NULL default '0.00',
`tran_cellall` decimal(11,2) NOT NULL default '0.00',
`tran_leavepdout_days` decimal(11,2) NOT NULL default '0.00',
`tran_leavepdout_rate` decimal(11,2) NOT NULL default '0.00',
`tran_uif_emp` decimal(11,2) NOT NULL default '0.00',
`tran_uif_com` decimal(11,2) NOT NULL default '0.00',
`tran_sdl` decimal(11,2) NOT NULL default '0.00',
`tran_paye` decimal(11,2) NOT NULL default '0.00',
`tran_staffloan` decimal(11,2) NOT NULL default '0.00',
`tran_unpaidleave_days` decimal(11,2) NOT NULL default '0.00',
`tran_unpaidleave_rate` decimal(11,2) NOT NULL default '0.00',
`tran_leave_taken` decimal(10,2) NOT NULL default '0.00',
`tran_sl_taken` decimal(11,2) NOT NULL default '0.00',
PRIMARY KEY (`tran_id`)
) TYPE=MyISAM PACK_KEYS=0 AUTO_INCREMENT=12 ;
#
# Dumping data for table `transactions`
#
INSERT INTO `transactions` VALUES (6, 1, 1, '3500.00', '29.84', '1.00',
'39.78', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '159.12',
'36.70', '36.70', '36.70', '366.96', '50.00', '0.00', '159.12', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (7, 1, 2, '2000.00', '17.04', '1.00',
'22.72', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '90.88',
'21.40', '21.40', '21.40', '213.98', '50.00', '0.00', '90.88', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (8, 1, 3, '9000.00', '68.18', '1.00',
'90.90', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '409.05',
'92.59', '92.59', '92.59', '925.91', '50.00', '0.00', '409.05', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (11, 2, 1, '3500.00', '29.84', '1.00',
'39.78', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '159.12',
'36.70', '36.70', '36.70', '366.96', '50.00', '0.00', '159.12', '0.00',
'0.00');
INSERT INTO `transactions` VALUES (10, 2, 7, '2200.00', '16.67', '1.00',
'22.22', '1.00', '40.00', '10.00', '20.00', '30.00', '0.00', '99.99',
'23.39', '23.39', '23.39', '233.89', '50.00', '0.00', '99.99', '0.00',
'0.00');
#
My query is as follows:
$variable3 $variable4
$variable5 $variable6";
}
}
?>
I concidered first selecting transactions that do exist in the period, but
then I am not sure how to put this into an array in order to exclude it. A
subquery doesn't work either and I'm using MySQL 4.0.24 will be upgrading
soon to MySQL 4.1.11
The query substring looked like this:
$t1 = 'employees';
$t2 = 'transactions';
$selectempquery = mysql_query("SELECT DISTINCT $t1.*, $t2.emp_id FROM $t1
LEFT JOIN $t2 ON $t2.emp_id = $t2.emp_id WHERE $t2.emp_id != (SELECT
`emp_id` FROM `transactions` WHERE `pp_id` = '$pp_id')");
What is wrong here? It returns no rows and I would expect it to.
Please help
--
PHP Database Mailing List (http://www.php.net/)
To unsubscr