List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 11 1999 7:38pm
Subject:Re: SELECT from several tables
View as plain text  
On Sat, 1999-09-11 20:30:04 +0300, Tim Groove wrote:
> How to SELECT from several tables with the same format, i.e. tables
> like hour_01, hour_02 etc., where each hour_## table has following
> structure:
>    uid int,
>    dt timestamp
> 
> I need to know how many times during day user "uid" inserted into
> hour_## so i need something like
> for (i=0;i<24;i++) {
>    select count(*) from tbl[i] where uid=###;
> }
> 
> Is it possible to make one good query instead of 24 selects?

You maybe could do that with sub-selects, but MySQL doesn't support
them yet.  So no, it can;t be done in a single query with MySQL.

> If not, what is the best way? Maybe temporary copying into one big table?

Actually, I think the best way is to change your table design!

For example, why not just use a single table, and then
   SELECT COUNT(*) FROM tab WHERE uid=###;

And to restrict the query to a certain hour:
    SELECT COUNT(*) FROM tab WHERE uid=###
                               AND dt BETWEEN 08:00:00 AND 08:59:59;
Or: SELECT COUNT(*) FROM tab WHERE uid=### AND HOUR(dt)=8;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
SELECT from several tablesTim Groove11 Sep
  • Re: SELECT from several tablesMartin Ramsch11 Sep
  • search engined e l   d h a n o a12 Sep
    • Re: search engineBenjamin David Hildred12 Sep
    • Re: search engineAlexander I. Barkov12 Sep
Re: SELECT from several tablesTim Groove12 Sep
  • Re: SELECT from several tablesMartin Ramsch12 Sep
    • Re: SELECT from several tablesPaul DuBois12 Sep