SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot croman's Avatar
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting Fields from Multiple Tables

    I appologize for yet another question, but I have been working on this one for hours and I just can not seem to get it. (hopefully Kevins book will arrive soon).

    I have a Database with lots and lots of records. Each one of these tables is designed the exact same.. just different data.

    I would like to do a query that goes through the entire DB (through all of the fields) and counts up how many times one of the fields matches a certain value.

    I have tried different things but it seems like I can not find a way to have it search through all of the Tables

    Sorry, I am new to this stuff and just learning

    Any help is appreciated.. Thanks

    Example: (just fake info)
    Tables names(football players, basketball players, hockey players)
    Fields (Name, team, salary)

    I would like to run a query that would scan through all of the Tables and tell me how for all players whos name is John, how many have a salary of 100k

  2. #2
    SitePoint Enthusiast vischo's Avatar
    Join Date
    Mar 2003
    Location
    Chicago, IL
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My guess:

    SELECT * FROM football players, basketball players, hockey players WHERE name="John"

  3. #3
    SitePoint Zealot croman's Avatar
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vischo
    My guess:

    SELECT * FROM football players, basketball players, hockey players WHERE name="John"

    The problem is, I have about 50 tables and it will grow by about 5 a week. So I need someway of being able to tell it to just go through all tables instead of specifying names

  4. #4
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by croman
    it will grow by about 5 a week
    That must be wrong, your database Must be badly designed, if this is some system then it shouldn't create 5 tables every week, that just cant be true, what kind of data do you have?
    - website

  5. #5
    SitePoint Zealot croman's Avatar
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by website
    That must be wrong, your database Must be badly designed, if this is some system then it shouldn't create 5 tables every week, that just cant be true, what kind of data do you have?
    I have a Database to hold Race Results

    Each Table is a Race.. each table then contains all of the fields such as Driver, Finish Position, Start Position, etc, etc..

    We add about 5 or more races each week

  6. #6
    SitePoint Addict sojomy's Avatar
    Join Date
    Jul 2002
    Location
    Dallas, TX
    Posts
    349
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not just have one table. Use the same columns you're using now in each table, but add a "RaceID" to distinquish which rows go together. Then you just need one table, right?

  7. #7
    o_O O_o BlueFire2k5's Avatar
    Join Date
    Mar 2003
    Location
    Sioux Falls, SD
    Posts
    475
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sojomy
    Why not just have one table. Use the same columns you're using now in each table, but add a "RaceID" to distinquish which rows go together. Then you just need one table, right?
    That would be much, much more efficient then what you are doing now.
    "Sa souvraya niende misain ye."
    - Robert Jordan, The Wheel of Time

  8. #8
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, what Sojomy said.

    If you are creating a table for each race, you really don't understand the power of a database, or the efficient organization of a database, and at the end of a year you will have 52 * 5 tables = 260 tables. Multiply that out over a few years and you will have something that is incredibly unwieldy and highly inefficient.
    John

  9. #9
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes what they all above said

    You never create a system that creates tables, the only thing the system/website should do is create lines in preexisting tables.
    - website


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •