0

I have searched, but can't seem to find how I do this. Basically I have two tables, and if this was slightly less complicated a simple join would work.

What I am trying to achieve is that each pupil chooses two subjects, and he scores a certain amount of points for each subject per week. Then I want to be able to display the pupil's name, his subject 1, then the score, then subject 2 and the score. With the query below, this obviously works for showing the score in subject 1, but I'm baffled as how to get subject 2.

$query = "SELECT pupils.name, pupils.subject1, pupils.subject2, subjects.week1 + week2 + week3 AS subject1points 
            FROM pupils, subjects 
            WHERE pupils.subject1 = subjects.subject";

$result = @mysql_query ($query);

if ($result) {
    echo....

Maybe some sort of subquery? Thanks in advance. Then of course I'm finishing off the table and displaying the results.

Jonathan Crowe
  • 5,793
  • 1
  • 18
  • 28
Rich Carter-Hounslow
  • 3
  • 1
  • 3
    Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). You will also want to [Prevent SQL Injection!](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – Jay Blanchard Nov 17 '14 at 22:55
  • without seeing your data structure we'd only be guessing. Please share that. – Jay Blanchard Nov 17 '14 at 22:56

3 Answers3

1

You can join to subjects three times, but you will need to alias them to disambiguate the columns:

SELECT 
    pupils.name, 
    pupils.subject1, 
    pupils.subject2,
    pupils.subject3,
    s1.week1 + s1.week2 + s1.week3 AS subject1points,
    s2.week1 + s2.week2 + s2.week3 AS subject2points,
    s3.week1 + s3.week2 + s3.week3 AS subject3points
FROM 
    pupils, subjects s1, subjects s2, subjects s3
WHERE 
    pupils.subject1 = s1.subject
    AND
    pupils.subject2 = s2.subject
    AND
    pupils.subject3 = s3.subject

In the above query, s1, s2, and s3 are the aliases. Once defined, you can use them wherever you would normally use the full table name.

Another way, and my personal preference, is to use the JOIN syntax:

SELECT 
    p.name, 
    p.subject1, 
    p.subject2,
    p.subject3,
    s1.week1 + s1.week2 + s1.week3 AS subject1points,
    s2.week1 + s2.week2 + s2.week3 AS subject2points,
    s3.week1 + s3.week2 + s3.week3 AS subject3points
FROM 
    pupils p
JOIN
    subjects s1 ON p.subject1 = s1.subject
JOIN
    subjects s2 ON p.subject2 = s2.subject
JOIN
    subjects s3 ON p.subject3 = s3.subject

In the above query, I also aliased pupils to p. I like this syntax more because it's very clear. If any of the pupils' subjects can be null (subject1, subject2, or subject3), then you may want to change these to LEFT JOINs so that you can still see the results from any non-null subjects.

Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • Thank you very much for that, it makes sense, I will put it into practice in the morning to see if it's all working for me. – Rich Carter-Hounslow Nov 17 '14 at 23:12
  • You're welcome. And please consider Jay's comments on your question, what's described by him there is very important. – Cᴏʀʏ Nov 17 '14 at 23:19
  • This all works brilliantly. How would I add though a ranking column to the results? I have tried looking at some kind of COUNT function but cannot get any of them working and I'm not sure it's because of the joins. – Rich Carter-Hounslow Nov 18 '14 at 15:06
  • Don't worry, I have added a very simple $rank = ($rank+1); Which is then displayed like this: '.$rank.' Does the trick – Rich Carter-Hounslow Nov 18 '14 at 15:13
0

Use three joins. I think this is what you want:

SELECT p.name, p.subject1, (s1.week1 + s1.week2 + s1.week3) as score1,
       p.subject2, (s2.week1 + s2.week2 + s2.week3) as score2,
       p.subject3, (s3.week1 + s3.week2 + s3.week3) as score3
FROM pupils p LEFT JOIN
     subjects s1
     ON p.subject1 = s1.subject LEFT JOIN
     subjects s2
     ON p.subject2 = s2.subject LEFT JOIN
     subjects s3
     ON p.subject3 = s3.subject;

Note you should learn to use proper explicit join syntax, particularly if you are relatively new to SQL. Now is the time to start good habits.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I would make a query joining a pupil table with 2 copies of subject table.

Edit
I won't bother coding the idea I expressed because others have done it. However, there are only 2 subjects, not 3 (and 3 weeks for score), but that just means leaving s3 out.

Community
  • 1
  • 1
Heimdall
  • 217
  • 2
  • 9