This particular query I am trying to do is using MySQL, but its
probably the same with any relational database:

Scenario:

I have 20 tables, all structured exactly the same. Each one only has
two columns, I'll call "week" which has a numerical value (1, 2, 3,
etc.) and "team" which has a sports team name (bears, tigers, tunas,
etc.).

I'm trying to write a query that will query all 20 tables, for just a
particular week #, where the team name is a particular team and give me
a count.

For example, for week #1, I want to know throughout all my tables, who
picked the tigers to win. So on all the tables where week=1, how many
values of "tigers" are there throughout all 20 tables. Obviously, the
max value obtained would be 20, the min would be 0.

It seems simple to me, but dangit I just can't get it to work.

Any help is greatly appreciated!

globring (AT) gmail (DOT) com wrote:
Can I ask a really dumb question: Why are there 20 tables, and not just
1?

globring (AT) gmail (DOT) com wrote:
SELECT team, COUNT(week)
FROM (
SELECT team, week FROM table01
UNION ALL SELECT team, week FROM table02
...
UNION ALL SELECT team, week FROM table19
UNION ALL SELECT team, week FROM table20
) AS composite_table
WHERE week = 1
GROUP BY team;

This is assuming your RDBMS supports subqueries as derived tables.

Though I agree with strawberry that it is unusual and probably not the
best design to have to do this. All your 20 tables should be combined
into one.

Regards,
Bill K.