mysqlstored-proceduresinner-joinselect-query

MySql StoredProcedure with inner join


I have a table named region having two columns : having region_id is primary key

       |region_id | region_code |
       | ----------+-------------+
       |         1 | Asia        |
       |         2 | Can         |
       |         3 | Cen         |
       |         4 | West        |
       |         5 | GNW         |
        ----------+-------------+

And i have another table employee , in which emp_id is primary key and region_id_fk is foreign key mapped to region table:

     +------+---------+---------+------------+---------------+---------+------------
    |emp_id| emp_name|global_id|region_id_fk|attendance_date|ispresent| is_billable|
    +------+---------+---------+------------+---------------+---------+---------
    |  1   | andrew  | candrew |          1 | 2017-02-13    |      1  |          1 |
    |  2   | andrew  | candrew |          1 | 2017-02-14    |      1  |          1 |
    |  3   | andrew  | candrew |          1 | 2017-02-15    |      1  |          1 |
    |  4   | andrew  | candrew |          1 | 2017-02-16    |      1  |          1 |
    |  5   | andrew  | candrew |          1 | 2017-02-17    |      0  |          1 |
    |  6   | simon   | csimon  |          1 | 2017-02-13    |      1  |          1 |
    |  7   | simon   | csimon  |          1 | 2017-02-14    |      1  |          1 |
    |  8   | simon   | csimon  |          1 | 2017-02-15    |      1  |          1 |
    |  9   | simon   | csimon  |          1 | 2017-02-16    |      1  |          1 |
    |  10  | simon   | csimon  |          1 | 2017-02-17    |      1  |          1 |
    |  11  | peter   | cpeter  |          2 | 2017-02-13    |      1  |          1 |
    |  12  | peter   | cpeter  |          2 | 2017-02-14    |      1  |          1 |
    |  13  | peter   | cpeter  |          2 | 2017-02-15    |      1  |          1 |
    |  14  | peter   | cpeter  |          2 | 2017-02-16    |      1  |          1 |
    |  15  | alvin   | calvin  |          2 | 2017-03-13    |      1  |          0 |
    |  16  | thomas  | thomas  |          2 | 2017-03-14    |      0  |          1 |
    |  17  | samuel  | csamuel |          2 | 2017-03-15    |      1  |          0 |
    |  18  | jackson | cjackson|          2 | 2017-03-16    |      1  |          0 |
    |  19  | clinda  | clinda  |          2 | 2017-03-17    |      1  |          1 |
    +--------+----------+-----------+--------------+-----------------+------

I already have this type of query where I used inner join and count in a stored procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS test.some_proc $$
CREATE PROCEDURE test.some_proc(IN in_is_billable INT,IN in_month INT,IN in_ispresent INT)  
BEGIN  
      DECLARE capacitycount INT;
      DECLARE hrs INT;
      SET hrs=8;
      SELECT COUNT(ispresent)*8 AS team_capacity, region_code 
      FROM employee emp
      INNER JOIN  region r ON r.region_id=emp.region_id_fk
      WHERE (is_billable=in_is_billable) AND
      (MONTH(attendance_date)=in_month) AND   
      (ispresent=in_ispresent);
END $$
DELIMITER ;

And its giving me below result: team capacity|region_code 128 |Asia

I want my stored procedure to give all the region code present in region table along with team capacity which i m calculating in stored procedure which is giving me correct answer.

The result should be like this but for now I am only getting team capacity for only one region.

 team capacity|region_code
    128          |Asia
    39           |Can
    68           |Cen   

Any help will be appreciated, Thanks in advance :)


Solution

  • you missed group by in your syntax

    DELIMITER $$
    DROP PROCEDURE IF EXISTS test.some_proc $$
    CREATE PROCEDURE test.some_proc(IN in_is_billable INT,IN in_month INT,IN in_ispresent INT)  
    BEGIN  
           DECLARE capacitycount INT;
           DECLARE hrs INT;
           SET hrs=8;
           SELECT COUNT(ispresent)*8 AS team_capacity, region_code 
           FROM employee emp
           INNER JOIN  region r ON r.region_id=emp.region_id_fk
           WHERE (is_billable=in_is_billable) AND (MONTH(attendance_date)=in_month) AND   
          (ispresent=in_ispresent)
           group by r.region_code;
    END $$
    DELIMITER ;