How can i use sub-query rather than join to join more than three tables using mysql

Databases
1

how can i use sub-query rather than join to join more than three tables using mysql.

              SELECT
          employment_status.staff_type As Appointment,
        COUNT(monthly_pay_register.age) as Staff_Count,
      monthly_pay_register.BASIC_SALARY  as Basic_Pay,
       monthly_pay_register.TOTAL_MONTHLY_ALLOWANCES as Total_Allowance,
      monthly_pay_register.MONTHLY_GROSS as Gross_Pay,
    monthly_pay_register.TOTAL_MONTHLY_DEDUCTIONS as Total_Deduction,
   CASE WHEN deduction_logs.deduction_name ='TAX' THEN deduction_logs.amount END as `TAX`,
  CASE WHEN deduction_logs.deduction_name ='PENSION_GOVT' THEN deduction_logs.amount 
   END as `PENSION_GOVT`,
   CASE WHEN deduction_logs.deduction_name ='PENSION_STAFF' THEN deduction_logs.amount 
   END as `PENSION_STAFF`,
             monthly_pay_register.MONTHLY_PAY as Netpay
          FROM (
               monthly_pay_register INNER JOIN deduction_logs ON
            monthly_pay_register.employee_info_employee_no = deduction_logs.employee_no
                    )
            INNER JOIN employment_status ON deduction_logs.employee_no = 
               employment_status.employee_no
                WHERE monthly_pay_register.`YEAR`=2017 and monthly_pay_register.`MONTH`='MARCH'

OR
deduction_logs.YEAR=2017 and deduction_logs.MONTH=‘MARCH’
GROUP BY employment_status.staff_type;
the query above is my join query but i want to convert to sub-query

2

What are you trying to accomplish? That would help us to answer your question.

JOINS are typically more efficient than sub-queries but there may be a case where it would be more efficient to use a sub-query…

3

sorry, i had to reformat your query in order to understand it…

SELECT employment_status.staff_type As Appointment
     , COUNT(monthly_pay_register.age) as Staff_Count
     , monthly_pay_register.BASIC_SALARY  as Basic_Pay
     , monthly_pay_register.TOTAL_MONTHLY_ALLOWANCES as Total_Allowance
     , monthly_pay_register.MONTHLY_GROSS as Gross_Pay
     , monthly_pay_register.TOTAL_MONTHLY_DEDUCTIONS as Total_Deduction
     , CASE WHEN deduction_logs.deduction_name ='TAX' 
            THEN deduction_logs.amount 
        END as `TAX`
     , CASE WHEN deduction_logs.deduction_name ='PENSION_GOVT' 
            THEN deduction_logs.amount 
        END as `PENSION_GOVT`
     , CASE WHEN deduction_logs.deduction_name ='PENSION_STAFF' 
           THEN deduction_logs.amount 
        END as `PENSION_STAFF`
     , monthly_pay_register.MONTHLY_PAY as Netpay
  FROM ( 
       monthly_pay_register 
INNER 
  JOIN deduction_logs 
    ON deduction_logs.employee_no = monthly_pay_register.employee_info_employee_no
       )
INNER 
  JOIN employment_status 
    ON employment_status.employee_no = deduction_logs.employee_no  
 WHERE monthly_pay_register.`YEAR` = 2017 
   and monthly_pay_register.`MONTH` = 'MARCH'
    OR deduction_logs.`YEAR` = 2017 
   and deduction_logs.`MONTH` = 'MARCH'
GROUP 
    BY employment_status.staff_type

i’m going to make a wild guess here… your query is performing very poorly and somebody told you that it might speed up if you converted the joins to subqueries

i can help you do that, but you have to help me – what are the primary keys and foreign keys of each of those three tables?

4

iddeduction_logs for deduction_logs table
idemployment_status for employment_status table
age for monthly_pay_register table
and here is what i user to join the tables because they all exists in them
employee_no
employee_info_employee_no

5

sorry, that doesn’t make any sense to me

e.g. how can age be the PK for monthly_pay_register? you would have only 1 row for age 27…

could you maybe do a SHOW CREATE TABLE for each table

6

hahaha… it is just a name sir
with an auto increment

7

@DaveMaxwell. trying to reduce the time use by the query

8

could you maybe do a SHOW CREATE TABLE for each table

9

@r937, pls dont really get what you mean by
SHOW CREATE TABLE for each table
pls explain

10

it’s a mysql command… you run it like you run a SELECT query –

SHOW CREATE TABLE  deduction_logs

it produces text, so you copy/paste the text here so we can see what the tables look like

11

deduction_logs CREATE TABLE deduction_logs (
iddeduction_logs int(10) unsigned NOT NULL AUTO_INCREMENT,
employee_no varchar(14) NOT NULL,
deduction_name varchar(45) NOT NULL,
ministry varchar(100) DEFAULT NULL,
department varchar(100) DEFAULT NULL,
amount decimal(50,2) NOT NULL,
month varchar(10) NOT NULL,
year int(10) unsigned NOT NULL,
datelog timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (iddeduction_logs) USING BTREE,
UNIQUE KEY DUPLICATE_DEDUCTION (employee_no,deduction_name,month,year) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=36296317 DEFAULT CHARSET=utf8

12

monthly_pay_register CREATE TABLE monthly_pay_register (
age bigint(11) DEFAULT NULL,
category varchar(45) DEFAULT NULL,
employee_info_employee_no varchar(10) NOT NULL,
surname varchar(45) NOT NULL,
first_name varchar(45) NOT NULL,
middle_name varchar(45) DEFAULT NULL,
title varchar(45) NOT NULL,
date_of_birth date NOT NULL,
marital_status varchar(45) NOT NULL,
sex varchar(45) NOT NULL,
state_of_origin varchar(45) NOT NULL,
nationality varchar(45) NOT NULL,
residential_city varchar(45) DEFAULT NULL,
residential_state varchar(45) NOT NULL,
type_of_accom varchar(45) NOT NULL,
mobile_phone varchar(45) DEFAULT NULL,
employee_info_entered_by varchar(45) NOT NULL,
employee_info_biometric_by varchar(45) DEFAULT NULL,
employee_info_entered_on timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
active tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
photo_url varchar(100) DEFAULT NULL,
activated_on timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
residential_lga varchar(45) DEFAULT NULL,
employment_number varchar(45) DEFAULT NULL,
entry_step_1st_emp varchar(2) DEFAULT NULL,
entry_grade_level_1st_emp varchar(2) DEFAULT NULL,
first_date_of_first_employment date DEFAULT NULL,
place_of_1st_employment varchar(200) DEFAULT NULL,
service_days_remaining bigint(11) DEFAULT NULL,
service_status varchar(45) DEFAULT NULL,
retirement_date date DEFAULT NULL,
current_grade_level varchar(45) NOT NULL,
current_step varchar(45) DEFAULT NULL,
employment_status_department varchar(150) NOT NULL,
current_designation varchar(100) NOT NULL,
appointment_status_current_emp varchar(45) DEFAULT NULL,
acting_appointment_current_emp varchar(45) DEFAULT NULL,
employment_status_ministry varchar(150) DEFAULT NULL,
salary_structure varchar(45) DEFAULT NULL,
pension_status tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
entry_establishment varchar(150) DEFAULT NULL,
account_number varchar(45) NOT NULL,
bank_name varchar(45) NOT NULL,
bank_branch varchar(45) DEFAULT NULL,
branch_name varchar(100) DEFAULT NULL,
account_type varchar(45) NOT NULL,
BASIC_SALARY decimal(50,2) DEFAULT NULL,
TOTAL_MONTHLY_ALLOWANCES decimal(50,2) DEFAULT NULL,
MONTHLY_GROSS decimal(50,2) DEFAULT NULL,
ALLOWANCES longtext,
TOTAL_MONTHLY_DEDUCTIONS decimal(50,2) DEFAULT NULL,
DEDUCTIONS longtext,
MONTHLY_PAY decimal(50,2) DEFAULT NULL,
YEAR int(10) unsigned DEFAULT NULL,
MONTH varchar(45) DEFAULT NULL,
PRID int(10) unsigned NOT NULL AUTO_INCREMENT,
locked tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
clone tinyint(1) NOT NULL DEFAULT ‘0’,
overpayment decimal(50,2) NOT NULL DEFAULT ‘0.00’,
PRIMARY KEY (PRID),
UNIQUE KEY DUPLICATE_PAYMENT (YEAR,MONTH,employee_info_employee_no) USING BTREE,
KEY MONTHLPIND (category,surname,first_name,middle_name,current_grade_level,current_step,employment_status_department,current_designation,employment_status_ministry,salary_structure,bank_name,branch_name,YEAR,MONTH,locked) USING BTREE,
KEY 87424fnenc (employee_info_employee_no),
KEY 9238924cds (MONTHLY_PAY),
KEY gdrghdrhe5y (YEAR,MONTH)
) ENGINE=InnoDB AUTO_INCREMENT=8944159 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

13

employment_status CREATE TABLE employment_status (
idemployment_status int(10) unsigned NOT NULL AUTO_INCREMENT,
employee_no varchar(45) NOT NULL,
first_date_of_first_employment date DEFAULT NULL,
confirmation_date_1st_emp date DEFAULT NULL,
entry_grade_level_1st_emp varchar(2) DEFAULT NULL,
entry_step_1st_emp varchar(2) DEFAULT NULL,
entry_designation_1st_emp varchar(45) DEFAULT NULL,
appointment_status_1st_emp varchar(45) DEFAULT NULL,
acting_appointment_ist_emp varchar(45) DEFAULT NULL,
date_of_last_promotion_1st_emp date DEFAULT NULL,
first_date_of_current_employment date DEFAULT NULL,
confirmation_date_current_emp date DEFAULT NULL,
current_grade_level varchar(45) NOT NULL,
current_step varchar(45) DEFAULT NULL,
department varchar(150) NOT NULL,
division varchar(45) DEFAULT NULL,
unit varchar(45) DEFAULT NULL,
current_designation varchar(100) NOT NULL,
appointment_status_current_emp varchar(45) DEFAULT NULL,
acting_appointment_current_emp varchar(45) DEFAULT NULL,
date_of_last_promotion_current_emp date DEFAULT NULL,
entered_by varchar(45) DEFAULT ‘System’,
entered_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ministry varchar(150) DEFAULT NULL,
salary_structure varchar(45) DEFAULT NULL,
pension_status tinyint(1) unsigned NOT NULL DEFAULT ‘2’,
entry_establishment varchar(150) DEFAULT NULL,
place_of_1st_employment varchar(200) DEFAULT NULL,
current_category varchar(45) DEFAULT NULL,
enable_payroll tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
ct tinyint(1) unsigned NOT NULL DEFAULT ‘0’,
modified_by varchar(45) DEFAULT NULL,
modified_on datetime DEFAULT NULL,
work_location varchar(160) DEFAULT NULL,
sub_treasury varchar(180) DEFAULT NULL,
school_name varchar(180) DEFAULT NULL,
rank varchar(50) DEFAULT NULL,
faculty varchar(45) DEFAULT NULL,
staff_type varchar(45) NOT NULL,
file_no varchar(65) DEFAULT NULL,
locked tinyint(1) DEFAULT ‘0’,
batch_id varchar(15) DEFAULT NULL,
unique_field varchar(255) DEFAULT NULL,
ministry1 varchar(255) DEFAULT NULL,
salary_str varchar(255) DEFAULT NULL,
department1 varchar(255) DEFAULT NULL,
PRIMARY KEY (idemployment_status),
UNIQUE KEY DUPLICATE_EMPLOYEE_EMPLOYMENT_STATUS (employee_no),
KEY EMPSTATIND (employee_no,first_date_of_first_employment,date_of_last_promotion_1st_emp,first_date_of_current_employment,current_grade_level,current_step,department,current_designation,date_of_last_promotion_current_emp,ministry,salary_structure,pension_status,current_category,enable_payroll) USING BTREE,
KEY curgra (current_grade_level,current_step)
) ENGINE=InnoDB AUTO_INCREMENT=58858 DEFAULT CHARSET=utf8

14

okay, now your problem is obvious

here’s your join, annotated –

  FROM ( 
       monthly_pay_register 
    /* UNIQUE KEY DUPLICATE_PAYMENT (YEAR,MONTH,employee_info_employee_no) */
INNER 
  JOIN deduction_logs 
    /* UNIQUE KEY DUPLICATE_DEDUCTION (employee_no,deduction_name,month,year) */
    ON deduction_logs.employee_no = monthly_pay_register.employee_info_employee_no
       )
INNER 
  JOIN employment_status 
    /* UNIQUE KEY DUPLICATE_EMPLOYEE_EMPLOYMENT_STATUS (employee_no) */
    ON employment_status.employee_no = deduction_logs.employee_no

suppose emplyoee 12345 has 24 rows in monthly_pay_register, one for each month for 2 years

now suppose that same employee has 3 deductions per month, for 72 rows

your join is producing 1728 rows for that employee!!!

no wonder it’s slow

your query is also likely producing incorrect data

solution: join the tables properly, on employee PLUS year PLUS month

strongly advised: set your SQL mode to ONLY_FULL_GROUP_BY

p.s. you do not need to change the joins to subqueries (whoever told you this nonsense is wrong)

15

so i should use ONLY_FULL_GROUP_BY
no GROUP_BY

16

but am using mysql.
not seeing ONLY_FULL_GROUP_BY as keyword

17

i gave you the link, here it is again — ONLY_FULL_GROUP_BY

18

@r937. can’t really understand anything their.
pls i love practical examples and how to implement.

can you help me with some code sample and steps

19

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.