CODEWITHSUNDEEP
mysqlsqlstored-procedures
Michelle
Michelle

Reputation: 39

Getting mySQL stored procedure results in another stored procedure

I need an example of a stored procedure that uses the results of two another stored procedures (or even just one) where the results are used as tables in the combining stored procedure. I could likely take it from there.

examples on web (multiple sites) and details at php.net are too simplistic.

The basic issue can be seen here:

SELECT ttd.person_id, weight, min(test_date)
FROM (
  CALL get_team_member_body_stats(1) AS tbs,   
  CALL get_team_member_first_last_test_date(1) AS ttd
  WHERE tbs.person_id = ttd.person_id
  AND (tbs.test_date = ttd.first_test OR tbs.test_date = ttd.last_test)
  GROUP BY ttd.person_id;
END

Thanks for any help. I've beat my head against this for several hours yesterday and today in online searching and experimenting.

Details:

Table 1

stored procedure get_team_member_body_stats(IN team_id INT) works over 5 tables (person, team, person_team, body_stats, person_body_stats) and produces:

person_id | body_stats_id | weight | test_date |   
  2         2               200      2011-01-01  
  4         3               250      2011-01-01  
  1         5               145      2011-03-01 
  2         6               210.4    2011-03-01 
  5         7               290      2011-03-01 
  1         8               140      2011-04-01 
  1         9               135      2011-05-01 
  4         11              245      2011-05-01

Table 2

stored procedure get_team_member_first_last_test_date(IN team_id INT) works over the same tables and produces:

person_id | first_test | last_test

1           2011-03-01   2011-05-01
2           2011-01-01   2011-03-01
4           2011-01-01   2011-05-01
5           2011-03-01   2011-03-01

Goal is to join these and produce:

Table 3

person_id | first_weight | last_weight

1           145            135
2           200            210.4 
4           250            245
5           290            290

Thanks

Upvotes: 2

Views: 3529

Answers (2)

Michelle
Michelle

Reputation: 39

It appears (per this community and because I couldn't find any examples of stored procedures using result sets from other stored procedures as tables in new select tables) that mysql either doesn't support this or that it is ridiculously hard to do what I hoped to do.

So rather than calling stored procedures I copied the queries into this final procedure.


    DELIMITER $$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `get_team_stats_change`(
        IN team_id INT
    )
    BEGIN
    SELECT SUM(start_weight) AS start_weight, 
        SUM(start_body_fat) AS start_body_fat, 
        SUM(current_weight) AS current_weight,
        SUM(current_body_fat) AS current_body_fat,
        SUM(weight_change) AS weight_change, 
        SUM(body_fat_change) AS body_fat_change FROM (
    SELECT ls.person_id, 
        fs.weight AS start_weight, fs.body_fat AS start_body_fat, 
        ls.weight AS current_weight, ls.body_fat AS current_body_fat,
        fs.weight - ls.weight AS weight_change, fs.body_fat - ls.body_fat AS body_fat_change
    FROM
    (SELECT ttd.person_id, bs.weight, bs.body_fat, bs.test_date
    FROM body_stats AS bs
    JOIN
        ((SELECT pbs.person_id,  min(bs.test_date)  AS first_test, max(bs.test_date) AS last_test   
        FROM body_stats AS bs,
            person_body_stats AS pbs,
            team_member AS tm,
            team AS t
        WHERE t.team_id = team_id
        AND tm.team_id = t.team_id
        AND tm.person_id = pbs.person_id
        AND pbs.body_stats_id = bs.body_stats_id
        AND tm.start_date  bs.test_date
        AND bs.test_date >= t.starting_date
        GROUP BY person_id) AS ttd,
        person_body_stats AS pbs)
    ON bs.test_date = ttd.first_test 
        AND pbs.person_id = ttd.person_id 
        AND pbs.body_stats_id = bs.body_stats_id) AS fs,

    (SELECT ttd.person_id, bs.weight, bs.body_fat, bs.test_date
    FROM body_stats AS bs
    JOIN
        ((SELECT pbs.person_id,  min(bs.test_date)  AS first_test, max(bs.test_date) AS last_test   
        FROM body_stats AS bs,
            person_body_stats AS pbs,
            team_member AS tm,
            team AS t
        WHERE t.team_id = team_id
        AND tm.team_id = t.team_id
        AND tm.person_id = pbs.person_id
        AND pbs.body_stats_id = bs.body_stats_id
        AND tm.start_date  bs.test_date
        AND bs.test_date >= t.starting_date
        GROUP BY person_id) AS ttd,
        person_body_stats AS pbs)
    ON bs.test_date = ttd.last_test 
        AND pbs.person_id = ttd.person_id 
        AND pbs.body_stats_id = bs.body_stats_id) AS ls
        WHERE ls.person_id = fs.person_id
       ) AS delta;
    END

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58441

I don't know how easily you can get these results from 5 tables incorporated into a temporary table or a view but essentially, following should do the same as what you are currently trying to using 3 stored procedures for.

SELECT person_id
       , (SELECT weight 
          FROM   FiveTablesResult 
          WHERE  person_id = ftr.person_id 
                 AND test_date = (SELECT MIN(test_date) 
                                  FROM   FiveTablesResult 
                                  WHERE  person_id = ftr.person_id)
         ) AS first_weight
       , (SELECT weight 
          FROM   FiveTablesResult 
          WHERE  person_id = ftr.person_id 
                 AND test_date = (SELECT MAX(test_date) 
                                  FROM   FiveTablesResult 
                                  WHERE  person_id = ftr.person_id)
         ) AS last_weight
FROM   FiveTablesResult ftr

Note that there is room for optimization but let us first get the correct results returned

Upvotes: 0

Related Questions