SOLVED, I created a new view with the current = 0 query and then run the other view on the new view.Worked a treat.
Good day all. I have a tricky MySql view that need altering and it is beyond me. I need to add gypsum.current = 1 to the first 3 SELECT COUNT queries so that they will not count where “current” = 0 in the table. Hope that is clear…
The query is :-
DELIMITER $$
USE `pattesti_testing`$$
DROP VIEW IF EXISTS `traffic_light2`$$
CREATE ALGORITHM=UNDEFINED DEFINER=`pattesti_pattest`@`%` SQL SECURITY DEFINER VIEW `traffic_light2` AS (
SELECT
‘id’ AS `id`,
(SELECT
COUNT(0)
FROM `gypsum`
WHERE ((`gypsum`.`test_frequency` = ‘3’)
AND (`gypsum`.`date_of_test` < (NOW() – INTERVAL 3 MONTH)))) AS `tot_3`, ""tot_3,tot_6,tot_12 need to have curent = 1 in the query somehow""
(SELECT
COUNT(0)
FROM `gypsum`
WHERE ((`gypsum`.`test_frequency` = '6')
AND (`gypsum`.`date_of_test` < (NOW() – INTERVAL 6 MONTH)))) AS `tot_6`,
(SELECT
COUNT(0)
FROM `gypsum`
WHERE ((`gypsum`.`test_frequency` = '12')
AND (`gypsum`.`date_of_test` < (NOW() – INTERVAL 12 MONTH)))) AS `tot_12`,
(SELECT
COUNT(0)
FROM `gypsum`
WHERE (`gypsum`.`test_frequency` = '3')) AS `items_3`,
(SELECT
COUNT(0)
FROM `gypsum`
WHERE (`gypsum`.`test_frequency` = '6')) AS `items_6`,
(SELECT
COUNT(0)
FROM `gypsum`
WHERE (`gypsum`.`test_frequency` = '12')) AS `items_12`)$$
DELIMITER ;
Thanks very Much G Styles
