Results 1 to 4 of 4

Thread: SQL Help

  1. #1
    Join Date
    Jan 2003
    Posts
    32

    Unanswered: SQL Help

    Let's say I want to post results for a player on my team, for all the tourneys he has played in. Eash tourney has been saved as a table. Let's say I want my scores. Bam. And the tables are named, tourney1, tourney2, tourney3...ect I need a statement that will get just the column named total for table tourney1, tourney2, and tourney3 and display them in different cells on the output table.
    SELECT total from (would i just separate the tables with a comma here?) WHERE name = 'Bam'

    Second, I want a column/field at the end that will get the AVG of the different scores returned.
    Third, I want a column/field after that that will have the AVG * .8 for a handicap.
    Can this be done?
    Bam

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Help

    Originally posted by bambamn007
    Let's say I want to post results for a player on my team, for all the tourneys he has played in. Eash tourney has been saved as a table. Let's say I want my scores. Bam. And the tables are named, tourney1, tourney2, tourney3...ect I need a statement that will get just the column named total for table tourney1, tourney2, and tourney3 and display them in different cells on the output table.
    SELECT total from (would i just separate the tables with a comma here?) WHERE name = 'Bam'

    Second, I want a column/field at the end that will get the AVG of the different scores returned.
    Third, I want a column/field after that that will have the AVG * .8 for a handicap.
    Can this be done?
    Bam
    Well if you had a table for each tourney, you would have made a big mistake! There should be ONE tourney table, with a column like tourney_number to identify the separate tourneys.

    You can get the average score for each player like this:

    SELECT name, AVG(total) FROM tourney
    GROUP BY name;

  3. #3
    Join Date
    Jan 2003
    Posts
    32
    Well, the problem is this. Each tourney has 4 rounds. So I have to clear the board after each tourney. I have a column for each round, and a column for total.
    So when I clear it in the actual DB, I rename it to another table name. SO now, my DB has the results table ( the one that the individual round scores get posted to) and the specific tourney tables.. Does that make sense?
    Look here to see the output on the page. This contains info from one table, but 2 different criteria's.

    http://www.gig-golf.com/GIG/ihtstandings2.asp

    So what you are saying, is I can't get info from multiple tables in 1 DB?
    Bam

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by bambamn007
    Well, the problem is this. Each tourney has 4 rounds. So I have to clear the board after each tourney. I have a column for each round, and a column for total.
    So when I clear it in the actual DB, I rename it to another table name. SO now, my DB has the results table ( the one that the individual round scores get posted to) and the specific tourney tables.. Does that make sense?
    Look here to see the output on the page. This contains info from one table, but 2 different criteria's.

    http://www.gig-golf.com/GIG/ihtstandings2.asp

    So what you are saying, is I can't get info from multiple tables in 1 DB?
    Bam
    Your design is totally the wrong approach for a relational database. You have a continually growing number of tables, and you want to query across the data in those tables. So every time you have a new tourney, you are going to have to amend all the queries that work across more than 1 tourney. It can be done, but no one does it that way!

    There is no need to "clear" the table for each new tourney. You just need a tourney column in the table. Queries that are only concerned with the current tourney could say "WHERE tourney_no = <latest tourney no>". Queries that want to gather statistics across many tourneys would simply not specify a tourney_no value.

    Where I work, each week I have to enter my timesheet details into a timesheet table. They do not "clear down" the table every week to make room for the new timesheet, they have a single timesheet table with a column called "week_commencing". The timesheet entry screen only shows 1 week at a time, but all the weeks are in the table and can be reported on.

Posting Permissions

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