SQL Query Question

Results 1 to 2 of 2

Thread: SQL Query Question

  1. #1
    Join Date
    Dec 1969

    Default SQL Query Question

    Hello, <BR><BR>I am trying to construct an SQL query for a project pertaining to a local olympics we are hosting. We want to display the results on our website. I want to show each participant&#039;s score (0 - 10.00) on a series of trials for their particular department.<BR><BR>The database is arranged with,<BR>one table for PARTICIPANTS, which includes the fields:<BR>PIN(PK), FIRSTNAME, LASTNAME<BR>one table for different TRIALS, which includes fields for: TRIALID(PK), COMPETITIONID, TRIALDESCRIP<BR>one table for TRIALSCORES, which includes fields for TRIALID(FK), PARTICIPANTID(FK), SCORE<BR>and a table to differentiate DEPARTMENTS, with fields for DEPARTMENTNAME, DEPARTMENTID (there are various stores participating which have the same department name), DPTMNTMNGRID (department manager ID)<BR><BR>What would be the most efficient way to achieve the following:<BR><BR>List all participants in a particular competition, (competitions are selected prior to this page from a drop down box) (the departmentID would be posted through form to this page)<BR><BR>list all trials, IE (archery range, relay race, balloon fight) as table headings,<BR><BR>list each participant&#039;s score for each trial <BR><BR>All of this in a table. I&#039;ve tried using IN JOINs, multiple WHERE criteria...and the problem I&#039;ve encountered the most is that it lists the same data multiple times every time it cycles to a new trial. Any help would be greatly appreciated.

  2. #2
    Join Date
    Dec 1969

    Default Should be all one query...

    But what order do you want things in??<BR> TRIALDESCRIP 1<BR> PARTICIPANT NAME: PARTICIPANT&#039;s TRIALSCORE SCORE<BR> ...<BR> TRIALDESCRIP 2<BR> PARTICIPANT NAME: PARTICIPANT&#039;s TRIALSCORE SCORE<BR> ...<BR>Or vice versa?<BR> PARTICIPANT NAME 1<BR> TRIALDESCRIP, PARTICIPANT&#039;s TRIALSCORE SCORE<BR> ...<BR> PARTICIPANT NAME 2<BR> TRIALDESCRIP, PARTICIPANT&#039;s TRIALSCORE SCORE<BR> ...<BR><BR>Except for the ORDER BY clause in the SQL, and then *which* field you change on, the code&#039;s the same.<BR><BR>SELECT P.FirstName, P.LastName, T.TrialDescrip, TS.Score, D.DepartmentName<BR>FROM Participants AS P, Trials AS T, TrialScores AS TS, Departments AS D<BR>WHERE D.DepartmentID = ###<BR>AND P.DepartmentID = D.DepartmentID<BR>AND TS.ParticipantID = P.PIN, <BR>AND T.TrialID = TS.TrialID<BR>ORDER BY TS.TrialDescrip, P.LastName, P.FirstName<BR><BR>** or **<BR><BR>ORDER BY P.LastName, P.FirstName, TS.TrialDescrip<BR><BR>*************<BR><BR>You substitute in the desired departmentid for the ### of course.<BR><BR>I notice you forgot to have the FK of departmentID in the PARTICIPANTS table. You *must* have that, or you don&#039;t have a relational database, do you?<BR><BR>And then just follow the principals of the "Categories with Subcategories" query in the ASPFAQs in the listings for "Databases, General". Link to ASPFAQs at top left of this page.<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts