#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0

    Mutli-table Query Syntax


    I have a database that contains tournament results from a sports league. Every table in the database has identical columns and column names. For example...

    Code:
                                  TABLE 1
       id       eventNum          location          place          score
    ----------------------------------------------------------------------
    player1      2013.01           chicago            3             47
    player2      2013.01           chicago            1             43
    player3      2013.01           chicago            4             48
    player4      2013.01           chicago            2             45
    
    
    
                                  TABLE 2
       id       eventNum          location          place          score
    ----------------------------------------------------------------------
    player1      2013.02           detroit            4             52
    player2      2013.02           detroit            1             46
    player3      2013.02           detroit            2             47
    player4      2013.02           detroit            3             49
    I have 100+ tables of tournament results. I would like to perform queries across multiple tables to look at an individual players results.

    For example, I would like to see the career results of a player. I was able to query this information, but it seemed extremely verbose and I can't seem to find any information online of a simpler way to do this if possible.

    So far I only have 5 tables entered into my database. I was able to query the information I wanted correctly using the following MySQL syntax...

    Code:
    Select * from 2013a where 2013a.id = 'player1'
    UNION
    Select * from 2013b where 2013b.id = 'player1'
    UNION
    Select * from 2013c where 2013c.id = 'player1'
    UNION
    Select * from 2013d where 2013d.id = 'player1'
    UNION
    Select * from 2013e where 2013e.id = 'player1';
    My query result table looked like this...
    Code:
       id       eventNum          location          place          score
    ----------------------------------------------------------------------
    player1      2013.01           chicago            3             47
    player1      2013.02           detroit            4             52
    player1      2013.03           atlanta            2             46
    player1      2013.04           houston            3             49
    player1      2013.05           detroit            1             44
    These are the exact results I was looking for, but I was wondering... Is there a better and less verbose way of performing this query?
    Last edited by Psyclone625; January 6th, 2014 at 04:23 PM. Reason: typo
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0
    Unfortunately, that is the downside of having all the tournament results in separate tables. I would suggest creating a new table that aggregates all tournament results, then query for the specific player results.

    Comments on this post

    • Psyclone625 agrees
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0
    Originally Posted by LaughingMan33
    Unfortunately, that is the downside of having all the tournament results in separate tables. I would suggest creating a new table that aggregates all tournament results, then query for the specific player results.
    OK, thanks.

    My MySQL program only displays 1000 rows. I can't image ever needing to display more than 150-200 rows from any query search.

    Can I make the table larger than 1000 rows but only display 1000 at a time? or do I need a different version of MySQL?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0
    Any system should allow you to create a table larger than 1000 rows. Are you using SQL Workbench? If so, it limits the number of results you see to 1000 rows even if more rows exist in the table.

    You can turn off this 1000 limit feature or increase the limit:

    Code:
    Go to Edit -> Preferences.
    Click on the SQL Editor tab.
    Under Query Editor, uncheck Limit Rows
    The limit exists so that you do not run out of memory. Or so that queries with a large amount of rows returned will process more quickly.

    Comments on this post

    • Psyclone625 agrees
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    13
    Rep Power
    0
    Originally Posted by LaughingMan33
    Any system should allow you to create a table larger than 1000 rows. Are you using SQL Workbench? If so, it limits the number of results you see to 1000 rows even if more rows exist in the table.

    You can turn off this 1000 limit feature or increase the limit:

    Code:
    Go to Edit -> Preferences.
    Click on the SQL Editor tab.
    Under Query Editor, uncheck Limit Rows
    The limit exists so that you do not run out of memory. Or so that queries with a large amount of rows returned will process more quickly.
    Yes, I am using MySQL Workbench and found the option in Preferences thanks to your post.

    THANKS!!!

IMN logo majestic logo threadwatch logo seochat tools logo