Multiple Recordsets SQL Query

[mattygriff]

Hi. I have an ASP page which needs to generate three recordsets, the first is a list of players, the second is a list of fixtures.

For each player in the set, the third recordset then needs to select from a table of scores any entries for that player but only for fixtures contained in the fixtures recordset.

My scores table contains scores for all players in all fixtures.

Can anyone help with the SQL?

 

[amorous]

Show us some data and the kind of result you want??


-VJ

 

[mattygriff]

OK, the first two SQL queries are :

strSQL_players = "SELECT * FROM players"

and

strSQL_fixtures = "SELECT * FROM fixtures WHERE team='FirstXI"

I'm looping round the first recordset and, for each player, want to create a third recordset containing all their scores from my 'scores' table but ONLY in fixtures which are in the second recordset.

The 'scores' table contains (apart from a record_id autonumber field) 'player_id', 'fixture_id' and 'score' fields.

Does that make things any clearer?

 

[amorous]

Ok from what i understand is that you need only one query instead of doing loops. You have to use Joins between these tables to get the result you want.

If you can provide some data in your tables and the result you want then we can come up with a query.

Show something like this:

Table- Players
playerid|field1|field2|
_______________________
1|John|something

Table2 - Fixtures
playerid|fixtureid|field1|field2|
_______________________
1|12|John|something


and the score table and result you want.


-VJ