[PHP-DB] MySQL JOIN query : Seeking solution - Please Help
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
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
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