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.

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

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');

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.

I would use really good nested functions

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?

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.

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

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.

Both queries give me "syntax error in FROM clause".

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.

I am using Microsoft Access.

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.

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.