Filter records from two joined views (In Full!)

[MargateSteve]

Firstly a huge apology for the double post. I accidentally hit post instead of preview midway through my first attempt and by the time I finished adding the rest of the data, the modify post time limit had expired.

 

Quite some time back, I was given help on here with aggregating fields from two different tables. It still works fine but I failed to foresee a future problem in that I need to use posted data to filter the query.

 

The data generated is statistics for football (soccer) players and is designed to show the number of minutes played per goal scored. Everything works fine but it only shows all data but I need to be able to filter the query between two dates at a time to some the statistics for separate seasons.

 

The first view gets all the games that a player has featured in (from 'appearances') including the number of minutes that he has been on the pitch and counts them by separate competition ('lgemins', 'facmins' etc in the counts towards the end of the view).

`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)) 
group by `players`.`player_id`;

 

The second view gets all the goals that a player has scored that a player has featured in (from 'goals') and counts them by separate competition (`lgegls`, `facgls` etc in the counts towards the end of the view).

`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)) 
group by `players`.`player_id`;

 

The query that brings it all together (showing just 'comp' = 1 data at the moment) with the actual minutes per game as 'lminpg' 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

 

All of the pages on the site use posted data to filter by season

if (isset($_GET['season_id']) ? $_GET['season_id'] : 1);
$season_id = $_GET['season_id'];

using the following in the query

LEFT JOIN seasons as S ON all_games.date 
BETWEEN S.season_start AND S.season_end
WHERE S.season_id = $season_id 

 

I need to implement something similar with the query from the views but have come up against blanks! If I use something like

WHERE view1.date BETWEEN '2010-08-01' AND  '2011-05-01'

which is equivalent to 'S.season_start AND S.season_end' for the 2010-11 season, some results are returned but nowhere near the correct number. I am assuming that the date fields from both views need to be joined but everything I have tried just returns the 'die' error message.

 

I am probably missing something very simple here but I just cannot get my head around it! If there is any more information required (such as test data) then just let me know.

 

As always, any pointers would be gratefully received.

Thanks in advance

Steve

[ebmigue]

Just a guess.

 

I've noticed your using LEFT JOIN.

 

That might have generated NULL attribute "values".

 

Thus your usual WHERE clause may not work as expected due to the NULL involved.

 

Check first the NULL columns before doing any restriction (WHERE clause).

 

Hope it helps.

[fenway]

Thus your usual WHERE clause may not work as expected due to the NULL involved.

Sure it will -- the OP is looking for matching rows, and since the WHERE clause doesn't refer to NULL values, the condition will be invariant.

 

Granted, there's no need for LEFT JOIN is this case, though.

[MargateSteve]

Sorry for the delay in getting back but have had a major virus issue!

 

The LEFT JOIN's are probably hangovers from when there was a WHERE clause on `games`.`date` to make sure only the relevant records where pulled for the view. If they are superfluous now I will happily have a go at re-writing it.

However, at the moment I think that I am going to have create a version of each view for each season with a WHERE `games`.`date` BETWEEN x AND x as I still cannot find a way to filter the date via the query on the page.

 

Steve

[fenway]

Then perhaps I'm not understanding what you're trying to achieve.

[MargateSteve]

Sorry, it always makes more sense in my head than on the screen!

 

The views and query that I currently have successfully generates the records that it should but it takes all records in the database.

 

However, I want to be able to use a WHERE clause on `games`.`date` in both views so I can show different records for each season. I have previously done this within the views by adding WHERE `games`.`date` BETWEEN x AND x but that then restricts the views to just those dates and cannot be used for any different dates.

 

What I am hoping to do is use a similar WHERE clause but within the query and not the views, so that I can alter just the query for each season and not have to create new views for each season.

 

Steve

[fenway]

So why not add the WHERE clause when querying the views? I'm confused.

[MargateSteve]

If I add the WHERE clause to the views it will be restricted to that one date range. I want to be able to generate separate statistics for different date ranges as time goes on ie. BETWEEN 2009-01-01 AND 2009-12-31 as one set of results and BETWEEN 2010-01-01 AND 2010-12-31 as another.

Steve

[ebmigue]

@OP

 

Enumerate the fields/attributes of each view that you want JOINed.

 

Enumerate the fields/attributes of the resulting JOINed views.

 

Tell us about the candidate keys of each views, and the resulting view.

 

Tell us about the criteria/predicate in your WHERE clause.

 

Most of the time, posting the actual source-code of views is not helpful since it will just produce confusion and too much information.

 

From a conceptual perspective the attributes of the views/table and result in question would suffice (w/ the their keys of course.)

 

Thank you and hope it helps.

[fenway]

If I add the WHERE clause to the views it will be restricted to that one date range. I want to be able to generate separate statistics for different date ranges as time goes on ie. BETWEEN 2009-01-01 AND 2009-12-31 as one set of results and BETWEEN 2010-01-01 AND 2010-12-31 as another.

Steve

No, not TO the views -- when you QUERY the views.

[MargateSteve]

And that was exactly my question in the first post. I tried WHERE view1.date BETWEEN '2010-08-01' AND  '2011-05-01'

in the query and although some results were returned there were fewer than there should have been.

My assumption was that the date fields in each view needed joining but could not work out how to do it.

 

Steve

[fenway]

Are you certain that it is not returning the correct result set?

[MargateSteve]

Are you certain that it is not returning the correct result set?

Absolutely. I have set up a page to show all the results that are being shown at http://www.margate-fc.com/views.php.

 

The first column shows the views exactly as I posted back at the start of the thread and there is no date clause in the query. This shows all records.

 

The second column also shows the views exactly as I posted back at the start but there is a date clause in the query. This returns some results but not all.

 

The third column has an added "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.

 

Steve

[fenway]

Well, not being able to see the dates in the output, it's hard to assess.