Defining views in mysql from complex queries - Try #2

Question

Hope this format is better... thanks to OMG ponies, Peter Lang, and astander for their patience in answering my first attempt.

I'm setting up a game in which each USER selects one CONTESTANT every WEEK, kind of like picking which football team you think will win. I need to create a view that has, for each combination of USER, CONTESTANT, and WEEK, how many times that USER has picked that particular CONTESTANT cumulatively up through that week. The data tables look like this:

Data table "contestants"
contestant  name
11          Aaron
12             Bob
13          Catherine
14          David

Data table "picks"
user    week    contestant
1001    1       11
1001    2       11
1002    1       12
1002    2       13

Now, I've got code that works and gives me the exact output that I want, but the problem is, I can't make a view out of it because there are subqueries. Here is the code and the correct output:

SELECT user, contestant ,week, count(valid_pick) num_picks 
  FROM
  (
  SELECT DISTINCT p.user , c.contestant, p.week  
    FROM contestants c , picks p
    ORDER BY user , contestant , week
  ) t1
  LEFT JOIN
  (
  SELECT user p_user , contestant p_contestant , week p_week ,
         1 as valid_pick  
    FROM picks p
    ORDER BY p.user , p_contestant , p_week
  ) t2
  ON t1.user = t2.p_user AND t1.contestant = t2.p_contestant 
     AND t2.p_week <= t1.week
GROUP BY user , contestant , week

user    contestant  week  num_picks
1001    11  1  1
1001    11  2  2
1001    12  1  0
1001    12  2  0
1001    13  1  0
1001    13  2  0
1001    14  1  0
1001    14  2  0
1002    11  1  0
1002    11  2  0
1002    12  1  1
1002    12  2  1
1002    13  1  0
1002    13  2  1
1002    14  1  0
1002    14  2  0

This has 0's in the right places and correctly counts the picks cumulatively by week.

But I really need this as a view, so I thought to just create the subqueries as mini-views. Here is the code:

CREATE OR REPLACE VIEW miniview1 AS
  SELECT DISTINCT p.user , c.contestant, p.week  
    FROM contestants c , picks p
    ORDER BY user , contestant , week
;
CREATE OR REPLACE VIEW miniview2 AS
  SELECT user p_user , contestant p_contestant , week p_week ,
         1 as valid_pick  
    FROM picks p
    ORDER BY p.user , p_contestant , p_week
 ;
CREATE OR REPLACE VIEW myview AS
SELECT user, contestant ,week, count(valid_pick) num_picks 
  FROM miniview1 t1
  LEFT JOIN miniview2 t2
  ON t1.user = t2.p_user AND t1.contestant = t2.p_contestant 
     AND t2.p_week <= t1.week
GROUP BY user , contestant , week

But what I get for "myview" is this:

user    contestant  week  num_picks
1001    14  1  1
1002    14  1  1
1001    14  2  1
1002    14  2  1
1002    11  1  1
1002    11  2  1
1001    12  1  1
1001    12  2  1
1001    13  1  1
1002    13  1  1
1001    13  2  1
1001    11  1  1
1001    11  2  2
1002    12  1  1
1002    12  2  1
1002    13  2  1

Clearly this is wrong. It seemed like such a simple substitution. I've checked miniview1 and miniview2 and they are exactly correct. Any ideas what is happening here?

Thanks, and thanks for your patience with a mysql (and stackoverflow) neophyte!

doxguy

Answer 1

miniview1:

CREATE OR REPLACE VIEW miniview1 AS
  SELECT p.user , c.contestant, p.week  
  FROM contestants c , picks p
  GROUP BY user , contestant , week;

miniview2:

   SELECT user p_user , contestant p_contestant , week p_week ,
     1 as valid_pick  
     FROM picks p;

now lets try a SELECT using miniview1 and miniview2

SELECT user, contestant ,week, count(valid_pick) num_picks 
  FROM 
   (  SELECT * from miniview1 ) t1
  LEFT JOIN
   (  SELECT * from miniview2 ) t2
ON t1.user = t2.p_user AND t1.contestant = t2.p_contestant 
AND t2.p_week <= t1.week
GROUP BY user , contestant , week;

this gives the same correct results as in your example. So it seems that is a problem with myview, not in miniview1 or miniview2.

I think that's because you cannot write the SELECT above as a VIEW. the SELECT uses subqueries, while the VIEW cannot use subqueries.

In other words: the SELECT statement above simply doesn't have the same semantics as the VIEW myview.

Answer 2

I think you want something like this:

Data table "contestants"
contestant  name
11          Aaron
12             Bob
13          Catherine
14          David

Data table "weeks"
contestant week
11         1
11         2
12         1
12         2

Data table "user"
user     name
1001     Fred
1002     Jane

Data table "picks"
user    week    contestant
1001    1       11
1001    2       11
1002    1       12
1002    2       13

select p.user, w.contestant, w.week,
    (select count(*)
        from picks p
        where p.week = w.week
        and p.contestant = w.contestant)
from user u
join weeks w 
join contestants c on c.contestant = w.contestant

Note that this code is untested.

I have left the names short and the database demornalised for clarity. The implementation could be tidied up a bit.

Answer 3

doxguy, you wrote:

... I've checked miniview1 and miniview2 and they are exactly correct ...

what would you expect as a correct result from miniview1?

When I tried your definition of miniview1 then I got results which are very obviously wrong to me:

select * from miniview1
user contestant week
1001 11         1
...
1002 13         1
1002 13         2
1002 14         1
1002 14         2

but - for example - there is no contest with contestant #14 at all.

EDIT: I know that this doesn't answer your question. It is merely something I found strange.