0

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 ASC

As 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

2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by urtrivedi
0
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.

You are in the right direction
remove this line from both views

AND DATE BETWEEN '2010-08-01' AND  '2011-05-01'

now add where condition in your query

.
.
.
LEFT JOIN view2 on view1.player_id=view2.player_id 
where  view1.DATE BETWEEN '2010-08-01' AND  '2011-05-01'
and view2.DATE BETWEEN '2010-08-01' AND  '2011-05-01'

ORDER BY lminpg ASC

Here you can set date dynamically using php post or get variable

IF above gives you unexpected result then you need to recode your view by only joining tables in view and then in your php code you count/sum and group those views.

If you count/sum in the viewitself you will not able to filter result by date properly.

Edited by urtrivedi: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.