Issue on Loading MySQL Temporary Table From Two Table Without Using Join
I am trying to create a Temprary Table in MySQL database called charts_ecolo_yes which it has 10 columns. What I need to do is populating the First 5 columns from a table called ecolo-cu-yes and the rest (5 columns) from another table called econo. As you can see I already achieved the first part but I am confused how to run another SELECT query at the rest of the code. Please be informed that both SELECT queries have WHERE clause WHERE ".$collm."=1
$query = "CREATE TEMPORARY TABLE IF NOT EXISTS `charts_ecolo_yes` (
`ecolo_sum_projects` decimal(12,7) NOT NULL,
`ecolo_sum_powerline` decimal(12,7) NOT NULL,
`ecolo_sum_roads` decimal(12,7) NOT NULL,
`ecolo_sum_cost` decimal(12,7) NOT NULL,
`ecolo_sum_penstlock` decimal(12,7) NOT NULL,
`econo_sum_projects` decimal(12,7) NOT NULL,
`econo_sum_powerline` decimal(12,7) NOT NULL,
`econo_sum_roads` decimal(12,7) NOT NULL,
`econo_sum_cost` decimal(12,7) NOT NULL,
`econo_sum_penstlock` decimal(12,7) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AS(SELECT COUNT(`project`) As ecolo_projects,
SUM(`powerline_length`) As ecolo_powerline,
SUM(`road_length`) As ecolo_roads,
SUM(`cost_per_year`) As ecolo_cost,
SUM(`penstlock`) As ecolo_penstlock
FROM `ecolo-cu-yes` WHERE ".$collm."=1 AND `animal` ='".$animal."'
// and Second Select FROM FROM `ecolo-cu-yes` WHERE ".$collm."=1
) ;";
$con->query($query);
$query2 = "SELECT ecolo_projects,
ecolo_powerline,
ecolo_roads,
ecolo_cost,
ecolo_penstlock FROM `charts_ecolo_yes`" ;
I tried to run the query by using SQL UNION command as:
AS(
SELECT COUNT(`project`) As ecolo_projects,
SUM(`powerline_length`) As ecolo_powerline,
SUM(`road_length`) As ecolo_roads,
SUM(`cost_per_year`) As ecolo_cost,
SUM(`penstlock`) As ecolo_penstlock
FROM `ecolo-cu-yes` WHERE c_1000=1
UNION
SELECT COUNT(`project`) As econo_projects,
SUM(`powerline_length`) As econo_powerline,
SUM(`road_length`) As econo_roads,
SUM(`cost_per_year`) As econo_cost,
SUM(`penstlock`) As econo_penstlock
FROM `econo` WHERE c_1000=1
);";
and merging two SELECT query in one as:
AS(SELECT
(
SELECT COUNT(`project`) As ecolo_projects,
SUM(`powerline_length`) As ecolo_powerline,
SUM(`road_length`) As ecolo_roads,
SUM(`cost_per_year`) As ecolo_cost,
SUM(`penstlock`) As ecolo_penstlock
FROM `ecolo-cu-yes` WHERE c_1000=1
)
(
SELECT COUNT(`project`) As econo_projects,
SUM(`powerline_length`) As econo_powerline,
SUM(`road_length`) As econo_roads,
SUM(`cost_per_year`) As econo_cost,
SUM(`penstlock`) As econo_penstlock
FROM `econo` WHERE c_1000=1
)
);";
but still not working!