Help needed - ODBC & complex SQL Statement

Help needed - ODBC & complex SQL Statement

Post by Mark Faulco » Thu, 05 Mar 1998 04:00:00



I'm writing a client/server application for use over our intranet; as such,
I'd like to keep the database queries as fast as possible. On one form, I
have a grid displaying records form the database in primary key order. These
records are fetched from the database through ODBC using a parameterized
query, as not all records are available to all users. Also, to keep it
running fast, the application only fetches about 100 records at a time;
returning all the records is impractical, as there are many thousands. My
questions are:

1. Is it possible for me specify the number of records to return as a
parameter? All my attempts at this have failed.

2. Is it possible to structure the query in such a way that it returns
records x through x+100, ie. 150 through 250?

 
 
 

Help needed - ODBC & complex SQL Statement

Post by Niels Berglun » Fri, 06 Mar 1998 04:00:00


Mark,

I assume you use Access as database as you mention parameterized queries?!
For answers see inline

[SNIP]

Quote:

>1. Is it possible for me specify the number of records to return as a
>parameter? All my attempts at this have failed.

If you use ADO you can set the Recordset's MaxRecords property.

Quote:

>2. Is it possible to structure the query in such a way that it returns
>records x through x+100, ie. 150 through 250?

Look at ADO's PageSize and PageCount properties.

Later...

Niels

 
 
 

1. Need help for complex (at least to me) SELECT statement

Thanks in advance for any help that you provide.

Without going into my specific project, I need to something like the
following (using sports as an example so everyone will understand):

3 Tables: League, Team, and Player.

A League has many teams, a team has many players and players have stats for
their goals

Pretty straight forward.  This select will get me a summary of the goals
scored for a team across all leagues and teams.

SELECT League.LeagueName, Team.TeamName, SUM(Player.Goals) As TeamGoals
FROM League INNER JOIN Team ON
                Team.LeagueID = League.LeagueID
             INNER JOIN Player ON
                Player.TeamID = Team.TeamID
GROUP BY League.LeagueName, Team.TeamName
ORDER BY LeagueName, TeamName

What I need in addition to that is to select the first two (or all if it
can't be done using just the first 2) player names for each team.

Something like (semi-pseudocoded)

SELECT League.LeagueName, Team.TeamName, SUM(Player.Goals) As TeamGoals,
CONCAT(TOP 2 Player.PlayerName) As TeamsPlayers
FROM League INNER JOIN Team ON
                Team.LeagueID = League.LeagueID
             INNER JOIN Player ON
                Player.TeamID = Team.TeamID
GROUP BY League.LeagueName, Team.TeamName
ORDER BY LeagueName, TeamName

Where CONCAT works a lot like SUM but instead of adding up the values, it
concatenates them together. (CONCAT is not a keyword....I wish it was)

Basically I want my result set to look like the following (hope this formats
ok):

LeagueName    TeamName    TeamGoals    TeamPlayers
----------------------------------------------------------------------------
------
NHL                   Redwings                   324     Yzerman, Shanahan

NHL is the League
Redwings is the Team
324 is the Sum of the # of goals scored by each player on the Redwings
Yzerman, Shanahan are 2 players on the redwings

Hope that explains the problem.  Any help would be greatly appreciated.

Thanks,
Dan

2. Clipper, memos, and hard retur

3. Complex SQL statement help

4. New Paradox-related web site

5. Help with complex SQL statement?

6. Open Server Gateway

7. Please help with Complex SQL SELECT statement

8. Contract Online 4.x ---> 6.x Migration in Florida

9. Help - Complex SQL SELECT statement!!!!

10. HELP needed on SQL & ODBC issue

11. SQL ANYWHERE & ODBC Help needed

12. help needed with ODBC & SQL