[PHP-DB] MySQL JOIN query : Seeking solution - Please Help

2005-04-20 Thread jinxed 
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:

?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

Re: [PHP-DB] MySQL JOIN query : Seeking solution - Please Help

2005-04-20 Thread Brent Baisley 
When checking for unmatched records, you use a left join as you are  
doing. This assures all records are retained from the left side table  
(i.e. employees), regardless if there is a matching record or not.  
Obviously if you have a filter on the left side table it will narrow  
down the result. To find unmatched records, just check if a field from  
the related table is NULL. Don't check for a non-matching id or some  
other unmatched key.

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_idemp_numemp_titleemp_surnameemp_name 
emp_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',

Re: [PHP-DB] MySQL JOIN query : Seeking solution - Please Help

2005-04-20 Thread graeme 
Get those with transaction into a php array
Get those without transaction into a php array
Then use array_diff() (or one of its variants)
graeme.
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_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:
?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