How to SELECT data using multiple tables

[banderson407]

I am new to MySql and PHP and am hoping that somone can help me with a query.

I have a table called Test
within it are several fields including:
course_ID
Test ID

so within this table data a Course (course_ID) can have several test_IDs associated with it.
So lets say course_ID (3) has test_IDs (2,4,5,7,9)
and course_ID (5) has test_IDs (1,15,22,44,33,55) associated with it.

I have another table called Test_results
within it are several fields including:
Member_ID
Test_ID
Final_Score

What I would like to do is query the Test_results table for a specific member (member_ID) but only bring back the data associate with a specific course (or the test_IDs for that course)

If I use :

SELECT * FROM "Test_results" WHERE Member_ID=12 ORDER BY Test_ID
 

I get all the test taken for member 12 in all courses he has taken tests in.

Is there some way to request just the results of all the tests in just one course ?

I hope I've made sense ,,,

Any help would be greatly appreciated.

Brad

[r035198x]

I am new to MySql and PHP and am hoping that somone can help me with a query.

I have a table called Test
within it are several fields including:
course_ID
Test ID

so within this table data a Course (course_ID) can have several test_IDs associated with it.
So lets say course_ID (3) has test_IDs (2,4,5,7,9)
and course_ID (5) has test_IDs (1,15,22,44,33,55) associated with it.

I have another table called Test_results
within it are several fields including:
Member_ID
Test_ID
Final_Score

What I would like to do is query the Test_results table for a specific member (member_ID) but only bring back the data associate with a specific course (or the test_IDs for that course)

If I use :
SELECT * FROM "Test_results" WHERE Member_ID=12 ORDER BY Test_ID

I get all the test taken for member 12 in all courses he has taken tests in.

Is there some way to request just the results of all the tests in just one course ?

I hope I've made sense ,,,

Any help would be greatly appreciated.

Brad

1.) "Specified course" means you are supplying a course_ID?
Then you want to do

select * from Test_results where (Member_ID = 12) and (Test_ID in (select Test_ID from Test where course_ID = "givenCourseID"));

N.B You should probably aliase your tables appropriately.

[banderson407]

Thank you so much for the quick response and I really appreciate you taking the time to help me as I struggle through this.

This is probably a real stupid question but what do you mean by aliase as it pertains to the tables ?

When I inserted the query you sent it errored out.

My internal variables are
Member_ID = $vlcmbid
Course_ID = $vlcsid

so the query looks like:

SELECT * FROM Test_results WHERE (Member_ID = $vlcmbid) AND (Test_ID IN (SELECT Test_ID FROM Test WHERE course_ID = $vlcsid));
 

Actually the PHP code looks like:

$query = "SELECT * FROM ".TABLE_PREFIX."Test_results WHERE (Member_ID = $vlcmbid) AND (Test_ID IN (SELECT Test_ID FROM Test WHERE course_ID = $vlcsid))";
$result = mysql_query($query);
 

When I execute the PHP script I doesn't return anything which causes it to error out after that when it tries to evaluate the results.

Thanks again as I am totally lost on how to resolve this.

Brad

[SkinHead]

Or You Could Use This :

SELECT TR.* FROM Test T, Test_results TR
WHERE T.Test_ID = TR.Test_ID
AND       T.Course_ID = GivenCourseID
AND       TR.Member_ID = GivenMemberID
 

I Haven't Checked It, But I Think It Will Work.

[r035198x]

Thank you so much for the quick response and I really appreciate you taking the time to help me as I struggle through this.

This is probably a real stupid question but what do you mean by aliase as it pertains to the tables ?

When I inserted the query you sent it errored out.

My internal variables are
Member_ID = $vlcmbid
Course_ID = $vlcsid

so the query looks like:

SELECT * FROM Test_results WHERE (Member_ID = $vlcmbid) AND (Test_ID IN (SELECT Test_ID FROM Test WHERE course_ID = $vlcsid));
 

Actually the PHP code looks like:

$query = "SELECT * FROM ".TABLE_PREFIX."Test_results WHERE (Member_ID = $vlcmbid) AND (Test_ID IN (SELECT Test_ID FROM Test WHERE course_ID = $vlcsid))";
$result = mysql_query($query);
 

When I execute the PHP script I doesn't return anything which causes it to error out after that when it tries to evaluate the results.

Thanks again as I am totally lost on how to resolve this.

Brad

See the sql posted above. The variables T and TR e.t.c are the aliases that I was talking about.

[Atli]

Or You Could Use This :

SELECT TR.* FROM Test T, Test_results TR
WHERE T.Test_ID = TR.Test_ID
AND       T.Course_ID = GivenCourseID
AND       TR.Member_ID = GivenMemberID
 

I Haven't Checked It, But I Think It Will Work.

This query could also be written like this. It may explain better (or not, what do I know!)

SELECT tr.*
FROM Test AS t
INNER JOIN Test_results AS tr
  ON tr.Test_ID = t.Test_ID
WHERE
  t.Course_ID = GivenCourseID
AND 
  tr.Member_ID = GivenMemberID
 

[Atli]

I have edited the thread's title to better describe it's contents.
Please read the Posting Guidlines before posting

Moderator