How to merge/combine multiple result sets in stored procedure in MySQL?-CodePudding

I have 3 stored procedure in MySQL db. I cannot merged/combined all of them in to one result sets. UNION, JOIN are not works. Create temp table as well. But when I run merged procedure it's always shows only first procedure result sets.

My stored procedures 1st have a parameter that I can change before I run it.

call ftht_away(1);
call ftht_home(1);
call ftht_table(1);

My 3 procedures:

DELIMITER //
CREATE  PROCEDURE `first_half_table`(IN league INT)
BEGIN
SELECT
`t`.`league_id` AS `league_id`,
tot.Team AS League,
SUM(tot.P) AS P,
SUM(tot.W) AS W,
SUM(tot.D) AS D,
SUM(tot.L) AS L,
SUM(tot.F) AS F,
SUM(tot.A) AS A,
SUM(tot.GD) AS GD,
SUM(tot.PTS) AS Pts
FROM
((SELECT
htft2.home AS Team,
1 AS P,
IF((htft2.home_ht_result > htft2.away_ht_result), 1, 0) AS W,
IF((htft2.home_ht_result = htft2.away_ht_result), 1, 0) AS D,
IF((htft2.home_ht_result < htft2.away_ht_result), 1, 0) AS L,
htft2.home_ht_result AS F,
htft2.away_ht_result AS A,
(htft2.home_ht_result - htft2.away_ht_result) AS GD,
(CASE
WHEN (htft2.home_ht_result > htft2.away_ht_result) THEN 3
WHEN (htft2.home_ht_result = htft2.away_ht_result) THEN 1
ELSE 0
END) AS PTS
FROM
htft2 UNION ALL SELECT
htft2.away AS away,
1 AS '1',
IF((htft2.home_ht_result < htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result < away_ht_result,1,0)',
IF((htft2.home_ht_result = htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result = away_ht_result,1,0)',
IF((htft2.home_ht_result > htft2.away_ht_result), 1, 0) AS 'IF(home_ht_result > away_ht_result,1,0)',
htft2.away_ht_result AS away_ht_result,
htft2.home_ht_result AS home_ht_result,
(htft2.away_ht_result - htft2.home_ht_result) AS 'GD',
(CASE
WHEN (htft2.home_ht_result < htft2.away_ht_result) THEN 3
WHEN (htft2.home_ht_result = htft2.away_ht_result) THEN 1
ELSE 0
END) AS 'CASE'
FROM
htft2) tot
JOIN teams2023 t ON ((tot.Team = CONVERT( t.team_name USING UTF8MB4))))
WHERE
(t.league_id = league)
GROUP BY tot.Team, t.league_id 
ORDER BY SUM(tot.PTS) DESC , GD DESC;
END //
DELIMITER ;

DELIMITER //
CREATE PROCEDURE `fh_away`(IN league INT)
BEGIN
    SELECT 
        `t`.`league_id` AS `league_id`,
        `tot`.`Team` AS `League`,
        SUM(`tot`.`P`) AS `P`,
        SUM(`tot`.`W`) AS `W`,
        SUM(`tot`.`D`) AS `D`,
        SUM(`tot`.`L`) AS `L`,
        SUM(`tot`.`F`) AS `F`,
        SUM(`tot`.`A`) AS `A`,
        SUM(`tot`.`GD`) AS `GD`,
        SUM(`tot`.`PTS`) AS `Pts`
    FROM
        ((SELECT 
            `htft2`.`away` AS `Team`,
                1 AS `P`,
                IF((`htft2`.`home_ht_result` > `htft2`.`away_ht_result`), 1, 0) AS `L`,
                IF((`htft2`.`home_ht_result` = `htft2`.`away_ht_result`), 1, 0) AS `D`,
                IF((`htft2`.`home_ht_result` < `htft2`.`away_ht_result`), 1, 0) AS `W`,
                `htft2`.`away_ht_result` AS `F`,
                `htft2`.`home_ht_result` AS `A`,
                (`htft2`.`away_ht_result` - `htft2`.`home_ht_result`) AS `GD`,
                (CASE
                    WHEN (`htft2`.`home_ht_result` < `htft2`.`away_ht_result`) THEN 3
                    WHEN (`htft2`.`home_ht_result` = `htft2`.`away_ht_result`) THEN 1
                    ELSE 0
                END) AS `PTS`
        FROM
            `htft2`) `tot`
        JOIN `teams2023` `t` ON ((`tot`.`Team` = CONVERT( `t`.`team_name` USING UTF8MB4))))
    WHERE
(t.league_id = league)
GROUP BY tot.Team, t.league_id 
    ORDER BY SUM(`tot`.`PTS`) DESC , `GD` DESC;
END //
DELIMITER ;

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `fh_home`(IN league INT)
BEGIN
SELECT 
        `t`.`league_id` AS `league_id`,
        `tot`.`Team` AS `League`,
        SUM(`tot`.`P`) AS `P`,
        SUM(`tot`.`W`) AS `W`,
        SUM(`tot`.`D`) AS `D`,
        SUM(`tot`.`L`) AS `L`,
        SUM(`tot`.`F`) AS `F`,
        SUM(`tot`.`A`) AS `A`,
        SUM(`tot`.`GD`) AS `GD`,
        SUM(`tot`.`PTS`) AS `Pts`
    FROM
        ((SELECT 
            `htft2`.`home` AS `Team`,
                1 AS `P`,
                IF((`htft2`.`home_ht_result` > `htft2`.`away_ht_result`), 1, 0) AS `W`,
                IF((`htft2`.`home_ht_result` = `htft2`.`away_ht_result`), 1, 0) AS `D`,
                IF((`htft2`.`home_ht_result` < `htft2`.`away_ht_result`), 1, 0) AS `L`,
                `htft2`.`home_ht_result` AS `F`,
                `htft2`.`away_ht_result` AS `A`,
                (`htft2`.`home_ht_result` - `htft2`.`away_ht_result`) AS `GD`,
                (CASE
                    WHEN (`htft2`.`home_ht_result` > `htft2`.`away_ht_result`) THEN 3
                    WHEN (`htft2`.`home_ht_result` = `htft2`.`away_ht_result`) THEN 1
                    ELSE 0
                END) AS `PTS`
        FROM
            `htft2`) `tot`
        JOIN `teams2023` `t` ON ((`tot`.`Team` = CONVERT( `t`.`team_name` USING UTF8MB4))))
    WHERE
(t.league_id = league)
GROUP BY tot.Team, t.league_id 
    ORDER BY SUM(`tot`.`PTS`) DESC , `GD` DESC;
END //
DELIMITER ;

enter image description here

SP1,2,3 picture and combined

CodePudding user response：

Modify your stored procedures. In each procedure replace the former SELECT with CREATE TEMPORARY TABLE total_data_N SELECT, where N is SP number.
Create stored procedure which will perform all additional actions:
- call all your SPs one-by-one
- select collected data
- drop temptables

CREATE PROCEDURE get_total_data (IN league INT)
BEGIN
  CALL ftht_away(league);
  CALL ftht_home(league);
  CALL ftht_table(league);
  SELECT * 
    FROM total_data_1
    JOIN total_data_2 USING (league_id, league)
    JOIN total_data_3 USING (league_id, league);
  DROP TEMPORARY TABLE total_data_1;
  DROP TEMPORARY TABLE total_data_2;
  DROP TEMPORARY TABLE total_data_3;
END

Execute created procedure by CALL get_total_data(1);, and it will produced combined rowset which you need in.

simplified DEMO