SQL query help with multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query help with multiple tables

Author

Topic

FickleLife
Starting Member

1 Post

Posted - 2011-07-12 : 15:02:21

I am creating a database for a running race. It's my first attempt at designing a DB to do this, so I'd appreciate any and all comments.

I have 4 tables:

cr_race (table name - list of races)
autoId, raceId, raceName
1, 23, Race 100mi
1, 24, Race 50mi
1, 25, Race 26.2mi

cr_runner (table name - List of runners competing)
autoId, runnerId, runnerName, runnerRaceId
1, 8373, Jim Bloggs, 23
2, 4323, James Roles, 23

cr_station (table name - represents all the aid stations during the race)
autoId, stationId, stationName, stationRaceId
1, 23, Aid Station One, 23
2, 24, Aid Station Two, 23

cr_time (table name - table of all runners times through each aid station)
autoId, timeRunnerId, timeStationId, timeSeconds, timeHMS
1, 8373, 23, 2324, 00:20:15
2, 8373, 24, 3567, 00:30:55
3, 4323, 23, 2223, 00:19:22
etc

What I need to do is combine all these tables to give a realtime table of results.

So in english, I need to:

Query the cr_runner table and get all records, join the cr_race table. Then I need to iterate through cr_time, looking for all records that match the runnerId with timeRunnerId and list them on a webpage in a table like so:

Name, Race, Aid 1, Aid 2, etc.
Jim Bloggs, Race 100mi, 00:20:15, 00:30:55, etc.

What's the best and most efficient way to query this?

Thanks very much for your help!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-07-13 : 03:08:34

try this

SELECT R.RUNNER_NAME,RACE_NAME,TIMEHMS FROM cr_runner R
INNER JOIN raceName N
ON N.runnerRaceId=R.raceId
INNER JOIN cr_time T
ON T.timeRunnerId=R.runnerId

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-15 : 16:55:18

>> I am creating a database for a running race. It's my first attempt at designing a DB to do this, so I'd appreciate any and all comments. <<

Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element _names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth.

What you did post was fundamentally wrong. Tables model either entities or relationships BUT NOT BOTH. That auto_id is total non-relational crap; you are picking up really bad habits. Since tables model sets, their names are plural or collective nouns.

CREATE TABLE Races
(race_nbr INTEGER NOT NULL PRIMARY KEY,
race_distance INTEGER NOT NULL
CHECK (race_distance > 0));

CREATE TABLE Runners
(runner_id INTEGER NOT NULL PRIMARY KEY,
runner_name VARCHAR(35) NOT NULL);

CREATE TABLE Aid_Stations
(race_nbr INTEGER NOT NULL
REFERENCES Races(race_nbr),
station_nbr INTEGER NOT NULL,
PRIMARY KEY (race_nbr, station_nbr),
station_name VARCHAR(35) NOT NULL);

CREATE TABLE Race_Results
(race_nbr INTEGER NOT NULL,
station_nbr INTEGER NOT NULL,
FOREIGN KEY (race_nbr, station_nbr)
REFERENCES Aid_stations (race_nbr, station_nbr),
runner_id INTEGER NOT NULL
REFERENCES Runners (runner_id),
PRIMARY KEY (race_nbr, runner_id),
runner_time TIME DEFAULT 0.0 NOT NULL);

See how everything is tied together with PK-FK? Absolutely no need for those silly non-relational auto_id. See the constraints? It takes about 1 year of full time SQL programming with a good Master programmer to learn this language. But iut is easier than Latin :)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources