0

I have three tables like this:

Student

stuNum | stuName
------------------
2012   | jack 
2013   | tom

Quiz

quizNum | quizName
------------------
1       | chapter 1
2       | chapter 2
3       | chapter 3

studentassessment

stuNum | quizNum | assessmentMark
-----------------------------------
2012   | 1       | 10
2012   | 2       | 8
2012   | 3       | 10
2013   | 1       | 5

I want to get result something like this

stuNum | stuName | Quiz Num | assessmentMark
--------------------------------------------
2012   | jack    | 1        | 10
2012   | jack    | 2        | 8
2012   | jack    | 3        | 10

description : all three table are connected.. i want to get the stuNum=2012, quizNum that all stuNum have done.

I tried several combination to fetch result but can't work. this is example combination that i tried :

$sql = "select a.stuNum,a.quizNum,a.assessmentMark from studentassessment a inner join student b on a.stuNum=b.stuNum inner join quiz c on a.quizNum=c.quizNum where b.stuNum='2012'"
Lisa
  • 9
  • 6
  • look like your question similar to http://stackoverflow.com/questions/13302898/mysql-join-query-on-three-tables-with-multiple-colums – bsting Nov 21 '14 at 05:37
  • 2
    c.quizName instead of a.quizName, you should be good or if you want num, then a.quizNum – radar Nov 21 '14 at 05:39
  • not really similar because that question already set the value..for this question, i can't get the quizNum value for stuNum=2012 – Lisa Nov 21 '14 at 05:40
  • sorry,im beginner in sql T__T – Lisa Nov 21 '14 at 05:44

2 Answers2

0

Query

SELECT a.stuNum,
b.stuName,
a.quizNum,
c.quizName,
a.assessmentMark
FROM studentAssessment a
JOIN student b
ON a.stuNum=b.stuNum
JOIN quiz c
ON a.quizNum=c.quizNum
WHERE a.stuNum=2012;

Output

+--------+---------+---------+-----------+----------------+
| STUNUM | STUNAME | QUIZNUM |  QUIZNAME | ASSESSMENTMARK |
+--------+---------+---------+-----------+----------------+
|   2012 |    Jack |       1 | chapter 1 |             10 |
|   2012 |    Jack |       2 | chapter 2 |              8 |
|   2012 |    Jack |       3 | chapter 3 |             10 |
+--------+---------+---------+-----------+----------------+

Fiddle demo

Ullas
  • 11,450
  • 4
  • 33
  • 50
0

Here .. try this out 

SELECT a.stuNum,b.stuName, a.quizNum, a.assessmentMark 
FROM studentassessment a 
INNER JOIN student b ON a.stuNum = b.stuNum 
WHERE b.stuNum='2012'

you do not need to put an inner join on quiz table since neither you are retrieving any value from it nor you are using it to deduce some relation in your output

Lalit Mehra
  • 1,183
  • 1
  • 13
  • 33