Back in February,smantscheff helped me with a problem where I was trying to aggregate data between two queries and after the suggestion of creating two views, everything worked perfectly. However, I failed to look ahead and see a potential future problem!
The original thread is http://www.daniweb.com/web-development/databases/mysql/threads/347065/1473368#post1473368 and quite simply, as the date range is specified in the views, everything is 100% restricted to those dates.
As the data are soccer statistics, I need to be able to use various different date ranges but do not really want to create new views for each date range.
I have actually trimmed the views that I was originally given to omit and fields that were not required and the current versions of them are
`view1` AS select
`games`.`all_games_id` AS `match_id`
,`games`.`date` AS `date`
,`games`.`time` AS `time`
,`games`.`comp` AS `competition`
,`games`.`round` AS `round`
,`games`.`replay` AS `replay`
,`games`.`home_team` AS `home_team`
,`games`.`away_team` AS `away_team`
,`games`.`home_goals` AS `home_goals`
,`games`.`away_goals` AS `away_goals`
,`games`.`attendance` AS `attendance`
,`games`.`report_url` AS `report_url`
,`games`.`photo_url` AS `photo_url`
,`games`.`notes` AS `notes`
,`games`.`extratime` AS `extra_time`
,`appearances`.`app_id` AS `app_id`
,`appearances`.`match` AS `match`
,`appearances`.`number` AS `number`
,`appearances`.`player` AS `player`
,`appearances`.`type` AS `type`
,`appearances`.`on` AS `on`
,`appearances`.`off` AS `off`
,`appearances`.`yellows` AS `yellows`
,`appearances`.`red` AS `red`
,`appearances`.`replaced` AS `replaced`
,`players`.`player_id` AS `player_id`
,`players`.`surname` AS `surname`
,`players`.`firstname` AS `firstname`
,`players`.`date_of_birth` AS `date_of_birth`
,`players`.`date_joined` AS `date_joined`
,`players`.`date_left` AS `date_left`
,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `lgest`
,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `lgesub`
,count((case when ((`games`.`comp` = 1) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `lgebench`
,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `facst`
,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `facsub`
,count((case when ((`games`.`comp` = 2) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `facbench`
,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `fatst`
,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `fatsub`
,count((case when ((`games`.`comp` = 3) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `fatbench`
,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `kscst`
,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `kscsub`
,count((case when ((`games`.`comp` = 4) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `kscbench`
,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 1)) then 1 else NULL end)) AS `lcst`
,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 2)) then 1 else NULL end)) AS `lcsub`
,count((case when ((`games`.`comp` = 5) and (`appearances`.`type` = 3)) then 1 else NULL end)) AS `lcbench`
,count((case when (`appearances`.`type` = 1) then 1 else NULL end)) AS `tst`
,count((case when (`appearances`.`type` = 2) then 1 else NULL end)) AS `tsub`
,count((case when (`appearances`.`type` = 3) then 1 else NULL end)) AS `tbench`
,(sum((case when (`games`.`comp` = 1) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 1) then `appearances`.`on` else NULL end))) AS `lgemins`
,(sum((case when (`games`.`comp` = 2) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 2) then `appearances`.`on` else NULL end))) AS `facmins`
,(sum((case when (`games`.`comp` = 3) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 3) then `appearances`.`on` else NULL end))) AS `fatmins`
,(sum((case when (`games`.`comp` = 4) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 4) then `appearances`.`on` else NULL end))) AS `kscmins`
,(sum((case when (`games`.`comp` = 5) then `appearances`.`off` else NULL end)) - sum((case when (`games`.`comp` = 5) then `appearances`.`on` else NULL end))) AS `lcmins`
,(sum(`appearances`.`off`) - sum(`appearances`.`on`)) AS `tmins`
from (`all_games` `games` join (`appearances` join `players` on((`appearances`.`player` = `players`.`player_id`))))
where ((`appearances`.`match` = `games`.`all_games_id`)
and (`games`.`comp` <> 6))
and date BETWEEN '2010-08-01' AND '2011-05-01'
group by `players`.`player_id`;and
`view2` AS select
`games`.`all_games_id` AS `match_id`
,`games`.`date` AS `date`
,`games`.`time` AS `time`
,`games`.`comp` AS `competition`
,`games`.`round` AS `round`
,`games`.`replay` AS `replay`
,`games`.`home_team` AS `home_team`
,`games`.`away_team` AS `away_team`
,`games`.`home_goals` AS `home_goals`
,`games`.`away_goals` AS `away_goals`
,`games`.`attendance` AS `attendance`
,`games`.`report_url` AS `report_url`
,`games`.`photo_url` AS `photo_url`
,`games`.`notes` AS `notes`
,`games`.`extratime` AS `extra_time`
,`goals`.`goal_id` AS `goal_id`
,`goals`.`match` AS `match`
,`goals`.`scorer` AS `scorer`
,`goals`.`goal_type` AS `goal_type`
,`goals`.`goal_time` AS `goal_time`
,`players`.`player_id` AS `player_id`
,`players`.`surname` AS `surname`
,`players`.`firstname` AS `firstname`
,`players`.`date_of_birth` AS `date_of_birth`
,`players`.`position` AS `position`
,`players`.`image` AS `image`
,`players`.`date_joined` AS `date_joined`
,`players`.`date_left` AS `date_left`
,count((case `games`.`comp` when 1 then 1 else NULL end)) AS `lgegls`
,count((case `games`.`comp` when 2 then 1 else NULL end)) AS `facgls`
,count((case `games`.`comp` when 3 then 1 else NULL end)) AS `fatgls`
,count((case `games`.`comp` when 4 then 1 else NULL end)) AS `kscgls`
,count((case `games`.`comp` when 5 then 1 else NULL end)) AS `lgecgls`
,count(`goals`.`goal_id`) AS `tgls`
from (`all_games` `games` join (`goals` join `players` on((`goals`.`scorer` = `players`.`player_id`))))
where ((`goals`.`match` = `games`.`all_games_id`)
and (`games`.`comp` <> 6))
and date BETWEEN '2010-08-01' AND '2011-05-01'
group by `players`.`player_id`;The query I currently use is
SELECT
view1.player_id as theRealId
, view1.surname as theRealSurname
, view1.firstname as theRealFirstname
, view1.lgemins/view2.lgegls as lminpg
, view1.date as theDate
, view1.*
, view2.*
FROM view1
LEFT JOIN view2 on view1.player_id=view2.player_id
ORDER BY lminpg ASCAs I said, this works fine but it will only work for one season (date range). I have tried several ways to try to move the date range from the views to the query but I am pretty sure that I am missing some required join between the two date fields.
I have set up a page with an example of my attempts at http://www.margate-fc.com/views.php
The first column shows the views exactly as I posted but with no date range in either the view or query. This shows all records from the database.
The second column also shows the views exactly as I posted but with no date range in the view but there is a date clause of "AND view1.date BETWEEN '2010-07-01' AND '2011-06-30'" in the query. This returns some results but not all. I have tried to also add "AND view2.date BETWEEN '2010-07-01' AND '2011-06-30'" but this gives the same records.
The third column has the "AND date BETWEEN '2010-07-01' AND '2011-06-30' " in both views and no date clause in the query. This shows almost the correct results although the first person with '-24.0000' shouldn't be there.
What I need to do is take the date clause from the views and instead use it in the query so that I can re-use the views with other date ranges.
Any suggestions, advice, pointers or offers to rip it all up and start again would be gratefully received.
Thanks in advance
Steve