Results 1 to 14 of 14

Thread: Help with a Query

  1. #1
    Join Date
    Dec 2007
    Posts
    8

    Help with a Query

    I have put together a database of my favorite basketball team. Each record is a game played. I wanted to query results on how the team has performed off a loss or off a win or when they were home off 3 straight away games. I assume it would have to be a subquery of some sort, but can not figure one out.

    Say my table would look something like this.

    ID (primary key), DATE, SITE, TEAM, RESULT, PF, PA

    200701 12/01/2007 A Southern W 68 51
    200702 12/03/2007 A Northern L 54 58
    200703 12/05/2007 A Eastern W 77 76
    200704 12/07/2007 H Western L 59 78

    How would I query those examples I have listed above which are based off the previous record(s).

    Any help from anyone would be greatly appreciated.

    Thank you again for time.

  2. #2
    Join Date
    Dec 2007
    Posts
    2

    Help with a Query

    Here are a couple of examples of some subqueries in a where statement that might help you resolve your post.

    Greg

    set nocount on
    create table Results (
    ID int,
    DATE smalldatetime,
    SITE char(1),
    TEAM varchar(20),
    RESULT char(1),
    PF tinyint,
    PA tinyint)

    insert into Results values(200701,'12/01/2007', 'A', 'Southern', 'W', 68, 51)
    insert into Results values(200702, '12/03/2007', 'A', 'Northern', 'L', 54, 58)
    insert into Results values(200703, '12/05/2007', 'A', 'Eastern', 'W', 77, 76)
    insert into Results values(200704, '12/07/2007', 'H', 'Western', 'L', 59, 78)

    -- display first home game after last away game
    select top 1 * from Results
    where DATE > (select max(DATE) from Results where SITE = 'A')

    -- display how team did after they last lose
    select top 1 * from Results
    where DATE > (select max(DATE) from Results where RESULT = 'W')

    drop table Results

  3. #3
    Join Date
    Dec 2007
    Posts
    8
    Thank you, but it is returning no results.

    Here is the query I am using

    SELECT TOP 1 *
    FROM test
    WHERE date>(SELECT max(date) FROM test WHERE su = 'w');

  4. #4
    Join Date
    Dec 2007
    Posts
    2
    What do you get from this query?

    SELECT max(date) FROM test WHERE su = 'w'

    Are there any records in your "test" table with a date greater then the one returned from the above query? If not that is why your a seeing no results.

  5. #5
    Join Date
    Dec 2007
    Posts
    3

    nested functions

    I would use really good nested functions

  6. #6
    Join Date
    Dec 2007
    Posts
    8
    There are no dates in the table greater than the date I get from this query.

    SELECT max(date) FROM test WHERE su = 'w'

    What are nested functions?

  7. #7
    Join Date
    Dec 2007
    Posts
    8
    ID DATE Site Team Opponent SU PF PA
    US200601 10/18/2006 H US Northern W 34 27
    US200602 10/21/2006 A US Eastern L 10 21
    US200603 10/24/2006 H US Southern L 14 16
    US200604 10/31/2006 A US Western L 10 32
    US200605 11/08/2006 H US Central L 20 23
    US200606 11/12/2006 H US Southwestern L 23 24
    US200607 11/15/2006 A US Northeastern L 9 22
    US200608 11/26/2006 A US Northwestern L 14 31
    US200609 11/29/2006 H US Eastern L 10 27
    US200610 12/02/2006 H US Western W 17 10
    US200611 12/08/2006 A US Northern L 26 31
    US200612 12/12/2006 A US Southern W 34 20
    US200613 12/15/2006 H US Northeastern W 27 21
    US200614 12/19/2006 H US Northwestern L 20 37
    US200615 12/22/2006 A US Central W 26 20
    US200616 12/27/2006 A US Southwestern L 20 27

    Let's say this would be my entire table. I want to show how the team did after an L in the query. Therefore my query result should show the following (games after an L):

    US200603 10/24/2006 H US Southern L 14 16
    US200604 10/31/2006 A US Western L 10 32
    US200605 11/08/2006 H US Central L 20 23
    US200606 11/12/2006 H US Southwestern L 23 24
    US200607 11/15/2006 A US Northeastern L 9 22
    US200608 11/26/2006 A US Northwestern L 14 31
    US200609 11/29/2006 H US Eastern L 10 27
    US200610 12/02/2006 H US Western W 17 10
    US200612 12/12/2006 A US Southern W 34 20
    US200615 12/22/2006 A US Central W 26 20

    These are the games following the games we had lost.

    Also say I want to query how our team did away after we played a home game. The results should look like this (games A after we played H):

    US200602 10/21/2006 A US Eastern L 10 21
    US200604 10/31/2006 A US Western L 10 32
    US200607 11/15/2006 A US Northeastern L 9 22
    US200611 12/08/2006 A US Northern L 26 31
    US200615 12/22/2006 A US Central W 26 20

    I know the answers I am looking for, but I just don't know how to get to them through an SQL Query. Eventually I would like to put the whole league into a table, and compare how are team had done in these situations compared to the entire league.

  8. #8
    Join Date
    Dec 2004
    Posts
    502
    How about these queries? This assumes that the id's are not out of order and can be used to determine the chronology of the games. I didn't use the date to determine chronology just in case two games were played on the same date.


    SELECT YourTable.*
    FROM YourTable
    JOIN (SELECT L.[id] AS LossId, MIN(A.[id]) AS NextGameId
    FROM YourTable AS A
    JOIN (SELECT [id]
    FROM YourTable
    WHERE su = 'L') AS L
    ON A.[id] > L.[id]
    GROUP BY L.[id]) AS NG
    ON YourTable.[id] = NG.NextGameId


    SELECT DISTINCT YourTable.*
    FROM YourTable
    JOIN (SELECT H.[id] AS HomeId, MIN(A.[id]) AS AwayGameId
    FROM YourTable AS A
    JOIN (SELECT [id]
    FROM YourTable
    WHERE site = 'H') AS H
    ON A.[id] > H.[id]
    WHERE A.site = 'A'
    GROUP BY H.[id]) AS AG
    ON YourTable.[id] = AG.AwayGameId
    Last edited by nosepicker; 12-19-2007 at 04:50 PM.

  9. #9
    Join Date
    Dec 2007
    Posts
    8
    Thank you. I will work on these and see what I get. Yes the ID is used as the primary key in chronological order for that reason in case 2 are on the same day.

  10. #10
    Join Date
    Dec 2007
    Posts
    8
    Both queries give me "syntax error in FROM clause".

  11. #11
    Join Date
    Dec 2004
    Posts
    502
    What RDBMS are you using? (I wrote these using SQL Server's T-SQL syntax)

    If you are in fact using SQL Server, please post your queries.

  12. #12
    Join Date
    Dec 2007
    Posts
    8
    I am using Microsoft Access.

  13. #13
    Join Date
    Dec 2007
    Posts
    8
    I figured it out, the issue was with JOIN. I had to use INNER JOIN. That works. However, Access is not letting me save the query now, which is weird.

  14. #14
    Join Date
    Dec 2004
    Posts
    502
    It's been a long time since I used Access, but if you're still having problems, you can possibly convert the subqueries in that statement (the parenthetical SELECT statements) into queries that you can INNER JOIN to in your larger SELECT statement.

Posting Permissions

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