Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    108

    Unanswered: Double query question - joining 3+ tables and find unique rec. in 2 columns

    I have years of sports (soccer) data having many countries, leagues and seasons in it, approx 180.000 records.

    Structure of the table is as follows:

    GameID
    Country
    Competition
    Round
    HomeClub
    AwayClub
    HomeScore
    AwayScore
    .... and so on (overtime yes/no, odds)

    I want to know stats that describe certain competition. So the idea is to group games belonging to a particular competition and calculate the stats like:

    Number of rounds per competition
    Number of games per round
    Number of goals scored on average
    Start date of a competition
    Total number of games in a competition .....

    I managed to do that via multiple (5-6) queries that use other queries. It's not neat but the numbers are accurate. I'm sure there's easier and classier solution but I'm too dumb to figure it out.

    I want to have all statistical data belonging to one competition as a single row in a table.

    So my first question is:

    Can I join 3 and more tables (in this case queries) in one?

    I would like to join all those queries having statistical data (each having Competition column) all in one table?

    ______________________

    Second question is:

    I would like to create lists of clubs pariticipating in each competition. In some competition clubs can play only home or away (not both).

    So I would need something like (in pseudocode):

    SELECT DISTINCT Clubs FROM HomeClubs column and AwayClubs column

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Have to Tryed breaking down the Querys

    ie
    what i do

    I could have up to 5 querys To get the Right answer

    query 1 would get in info I want well closed to is it
    then use query 2 to read query 1 to get it look closer
    then use query 3 to read query 2 to get it even closer

    all you need to is call the last query which call the one be for that and so on ...

    The worst I have done and this is over the top a monthly report
    that run a mk table them append some data form 4 differance databases.
    then the report get printed

    but the way it is done is
    each query does 1 small step of the job
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first question: yes

    second question:
    Code:
    SELECT HomeClubs AS Clubs
      FROM daTable
    UNION
    SELECT AwayClubs 
      FROM daTable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2007
    Posts
    108
    Have to Tryed breaking down the Querys

    ie
    what i do

    I could have up to 5 querys To get the Right answer
    That's exactly what I did to get the answer

    For example this query groups each competition, then each round in a competition (league) and counts number of games played in each round! The query is called [BE Games per round].

    Code:
    SELECT [BetExplorer Table Definition].League, [BetExplorer Table Definition].Round,
     Count([BetExplorer Table Definition].Round) AS [Games per round]
    FROM [BetExplorer Table Definition]
    GROUP BY [BetExplorer Table Definition].League, [BetExplorer Table Definition].Round;
    The second query then calculates totals from the above query, telling me average number of games per round/per league, telling me maximum number of games played in a single round of a particular competition and so on!

    Code:
    SELECT DISTINCTROW [BE Games per round].League, 
    Sum([BE Games per round].[Games per round]) AS [Sum Of Games per round], 
    Avg([BE Games per round].[Games per round]) AS [Avg Of Games per round], 
    Min([BE Games per round].[Games per round]) AS [Min Of Games per round], 
    Max([BE Games per round].[Games per round]) AS [Max Of Games per round], 
    StDev([BE Games per round].[Games per round]) AS [STDEV Of Games per round]
    FROM [BE Games per round]
    GROUP BY [BE Games per round].League;
    Now I have tons of these query on query scenario calculations for various stats. Each query has the same structure:

    League/Competition
    ....
    some stats (number of goals, number of rounds, number of overtime games, nubmer of games having odds listed etc.)
    ....

    I want to join the results of all those queries in a single table (on the same league/competition field) where I would have a clear single-row view of all the relevant stats I need for a single competition/league.

  5. #5
    Join Date
    Apr 2007
    Posts
    108
    to r937

    First answer: figured so, the question is HOW?

    Second answer: Thanks, worked like a charm (although figuring why UNION query won't work at first gives you a bit of headache until you figure out you have to tell Access specifically that you want union query)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Riorin
    First answer: figured so, the question is HOW?
    you had it right already -- with joins

    "I would like to join all those queries having statistical data (each having Competition column) all in one table?"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2007
    Posts
    108
    Solved.

    Created Master Query with one field Competition/League, opened up Relationships and linked/joined each statistical query to the master query via that field effectively creating one-to-one-I-have-all-the-data-I-need-in-one-row table!

    Done in 2 minutes using visual Query Builder that results in

    SELECT ****** bla, bla *******
    FROM (((((((((((((( gazilion parenthesis JOIN some table (((((((( gazilion-1 parenthesis some other table etc. to again gazillion parenthesis ))))))))))))))

    that would be unwritable for a sane human.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Riorin
    that would be unwritable for a sane human.
    what does this say about the engineers who coded the access query builder to create that crap?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    what does this say about the engineers who coded the access query builder to create that crap?
    Based on my last experience with the SQL Server query builder (some years ago now), they were seconded to the SQL Server team too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2007
    Posts
    108
    what does this say about the engineers who coded the access query builder to create that crap?
    That costs for maintainance of outdated androids is likely to rise due to post-coding emotional drainage crisis.

Posting Permissions

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