Need SQL to Select From one table with a relation ship in linking table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I Have tables
(a)

ExerciseID      ExerciseName                    MuscleTarget    SetsReps
1               FreeHand Jump Squat             Quadriceps	2 sets of 15 reps
2               Dumbbel Lunges                  Quadriceps	3 sets of 8-10
3               Dumbbel Step ups                Quadriceps	3 sets of 8-10
4               Stiff-Legged Barbell Dead Lift  Hamstrings	3 sets of 8-10 reps
5               Lying Leg Curls                 Hamstrings	3 sets of 8-10 reps
6               Leg Extensions                  Hamstrings	3 sets of 8-10 reps
7               Leg Press                       Quadriceps	3 sets of 8-12 reps

And
(b)

TrainingProgramID	ExerciseID	DayNumber
1	                7	        1
1	                8	        1
1	                9	        1
1	                10	        4
1	                11	        4
1	                12	        4

I Need Sql to select from table (a) where in table (b) DayNumber =1
Ive tried :

SQL

SELECT DISTINCT Exercise.ExerciseName, Exercise.MuscleTarget, Exercise.SetsReps, TrainingProgramExercise.DayNumber
FROM Exercise, TrainingProgramExercise
WHERE TrainingProgramExercise.DayNumber = 1

Tried a join as well but not working
Please Help

[edit]Code blocks added - OriginalGriff[/edit]

Posted 9-Sep-11 6:03am

SuperTeagz

Updated 9-Sep-11 6:22am

Corporal Agarn

v3

Add a Solution

Comments

Corporal Agarn 9-Sep-11 12:21pm

What did you try as your join?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Corporal Agarn · Accepted Answer · 2011-09-09T06:24:00

Solution 1

Did you try:

SQL

SELECT DISTINCT Exercise.ExerciseName, Exercise.MuscleTarget, Exercise.SetsReps, TrainingProgramExercise.DayNumber
FROM Exercise
INNER JOIN TrainingProgramExercise 
ON Exercise.ExerciseID = TrainingProgramExercise.ExerciseID
WHERE TrainingProgramExercise.DayNumber = 1

Posted 9-Sep-11 6:24am

Corporal Agarn

Comments

Wendelius 9-Sep-11 12:26pm

Almost identical :) my 5

SuperTeagz 9-Sep-11 12:37pm

Thanks A lot This works..
I tried:
SELECT DISTINCT Exercise.ExerciseName, Exercise.MuscleTarget, Exercise.SetsReps, TrainingProgramExercise.DayNumber
FROM Exercise
INNER JOIN TrainingProgramExercise
ON Exercise.ExerciseID = TrainingProgramExercise.ExerciseID But had This the other way around

WHERE TrainingProgramExercise.DayNumber = 1

Wendelius 9-Sep-11 14:24pm

BAsed on your data example I don't think you need the DISTINCT keyword. That was the difference between dj55 and my answers

Wendelius · Accepted Answer · 2011-09-09T06:25:00

Did you try something like:

SQL

SELECT Exercise.ExerciseName, 
       Exercise.MuscleTarget, 
       Exercise.SetsReps, 
       TrainingProgramExercise.DayNumber
FROM  Exercise, 
      TrainingProgramExercise
WHERE TrainingProgramExercise.DayNumber = 1
AND   Excercise.ExerciseID = TrainingProgramExercise.ExcerciseID