Something like this: SELECT DISTINCT $t1.*, $t2.emp_id FROM $t1 LEFT JOIN $t2 ON $t1.emp_id=$t2.emp_id WHERE NOT $t1.emp_termdate AND $t2.pp_id IS NULL
To get a quicker response to your questions in the future, don't post sample data and full table descriptions. Summarize your table structure and data to only the relevant pieces. What you posted is a lot to read through for the fairly straightforward answer you needed. For instance, I don't know what $t1.emp_termdate is and I really didn't read through your post to figure it out. I assume it's a form of boolean value ( 0, 1, Y, N, etc.), but I can't tell from the query. Comparison should be obvious. Remember, a boolean could be three possible values: True, False, NULL.
On Apr 20, 2005, at 10:09 AM, jinxed wrote:
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_id emp_num emp_title emp_surname emp_name emp_initials
emp_termdate // etc.
INSERT INTO `employees` VALUES (1, 'EMP001', 'Mrs.', 'Surname1', 'Name1',
'N1.', '0000-00-00');
INSERT INTO `employees` VALUES (2, 'EMP002', 'Mr.', 'Surname2', 'Name2',
'N2.', '0000-00-00');
INSERT INTO `employees` VALUES (3, 'EMP003', 'Mr', 'Surname3', 'Name3',
'N3.', '0000-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.', '0000-00-00');
INSERT INTO `employees` VALUES (7, 'EMP007', 'Mr.', 'Surname7', 'Name7',
'N7.', '0000-00-00');
# Table structure for table `payperiods`
pp_id pp_period pp_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:
<?php
// IF COUNT OF EMPLOYEES DIFFER WITH COUNT OF TRANSACTIONS, DO THIS
// DISPLAY ONLY EMPLOYEES THAT DON'T HAVE TRANSACTIONS FOR THE
CURRENT ("active") PERIOD.
else {
$t1 = 'employees';
$t2 = 'transactions';
$selectempquery = mysql_query("SELECT DISTINCT $t1.*, $t2.emp_id
FROM $t1
LEFT JOIN $t2 ON $t1.emp_id=$t2.emp_id
WHERE NOT $t1.emp_termdate
AND ($t2.pp_id != '$pp_id' OR $t2.pp_id IS NULL)"); // This
doesn't work right
// if i replace the above AND with:
AND $t2.emp_id IS NULL"); // it won't return any rows if
employees have transactions in other periods,
// because it searches the ON $t1.emp_id = $t2.emp_id and doesn't
// concider
the period at all
// Combining both these in the query just refuses to return any
results.
while ($row = @mysql_fetch_array($selectempquery)) {
$variable3=$row['emp_num'];
$variable4=$row['emp_title'];
$variable5=$row['emp_initials'];
$variable6=$row['emp_surname'];
echo "<tr><td width=75 height=21> $variable3 </td><td
width=225> <a
href=".WEB_ROOT."procpayslip1.php? pp_id=$pp_id&emp_num=$variable3>$variable4
$variable5 $variable6</a></td></tr>";
}
}
?>
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 unsubscribe, visit: http://www.php.net/unsub.php
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
