SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    188
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating a temporary table

    Hello,

    I have a table that contains an id, workoutname, and 3 exercises.

    WORKOUT
    ID | workoutName | exercise1 | exercise2 | exercise3

    The value stored in the exercise field is the id of the exercise in my exercise table.

    EXERCISE
    ID | exerciseName | weight | reps | time

    I would like to create a query that will list the following

    ID | workoutName | exercise1Name | exercise1Weight | exercise1Reps | exercise1Time| etc...

    I am running into roadblocks when I try to list it that way. Any hints or tips would be much appreciated. Perhaps I need to make a temporary table?

    Thank you

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What happens when a workout has 4 exercises? 10? 100? Are you going to expand that table to have 100 columns?

    You should create a separate table to hold the (workout / exercise) pairs. Insert one row for each exercise in a workout.

    Then you'll retrieve all the names, weights, reps for a workout with a single simple query (SELECT name, weight, reps FROM workouts INNER JOIN exercises ON workout.ID = exercises.workout_id).

    Formatting it into a horizontal row, if that's how you want to display it to the user, is the job of your program. The way you represent the data in the database should not be determined by how you're going to format it for output.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    The way you represent the data in the database should not be determined by how you're going to format it for output.
    truth

    there is no need for a temporary table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    188
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    What happens when a workout has 4 exercises? 10? 100? Are you going to expand that table to have 100 columns?

    You should create a separate table to hold the (workout / exercise) pairs. Insert one row for each exercise in a workout.
    Thanks for the comment Dan, The tables I showed were truncated for simplicity, I apologize if that led to confusion.

    I agree that the final formatting is the job of my code for output, not the data.

    Rather than having an workout table that holds the workout name and all of the exercises associated with it, you are suggesting a table that would hold the workout/exercise pairs.

    Workout
    workoutId | exerciseId
    workoutId | exerciseId
    workoutId | exerciseId

    Thank you for the guidance, it was what I needed to get my head around this.


Bookmarks

Posting Permissions

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