# Related Sites > SQL Course >  Help with a Query

## ejjonesjr

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.

----------


## Greg Larsen

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

----------


## ejjonesjr

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

----------


## Greg Larsen

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.

----------


## t3chn0n3rd

I would use really good nested functions

----------


## ejjonesjr

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?

----------


## ejjonesjr

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.

----------


## nosepicker

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

----------


## ejjonesjr

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.

----------


## ejjonesjr

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

----------


## nosepicker

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.

----------


## ejjonesjr

I am using Microsoft Access.

----------


## ejjonesjr

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.

----------


## nosepicker

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.

----------

