Re: 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_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>&nbsp;$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