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_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 THI
S
>         // 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>$variable
4
>$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
>
>
>

--
Experience is a good teacher, but she sends in terrific bills.

Minna Antrim