0

I currently have an SQL statement to fill a table with a student name and the name of a sport which they are taking in a given term. The name of the sport is stored in a sports table with a Sport_ID. The Sport_ID then corresponds with a Choice_ID in the choices table. This Choice_ID is then put in the column for each term labelled T1_Choice, T2_Choice and T3_Choice in the table Student_Choices. How would I get T2_Choice and T3_Choice from my SQL statement? Thanks for the help.

DB Fiddle Link

$stmt = $conn->prepare(
              "SELECT st.Name AS student, s.Name AS sport
              From Sports AS s INNER JOIN Choices AS c
              ON s.Sport_ID = c.Sport_ID INNER JOIN Student_Choices AS sc
              ON sc.T1_Choice = c.Choice_ID INNER JOIN Students AS st
              ON st.Username = sc.Username
              ");
            $stmt->execute();
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
              echo '<tr>
              <td>'.$row['student'].'</td>
              <td>'.$row['sport'].'</td>
              <td>'.$row['sport'].'</td>
              <td>'.$row['sport'].'</td>
              </tr>
              ';
            }
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Toby Dixon Smith
  • 45
  • 9
  • 1
    What is the table structure of `Student_choices` table ? It looks like a bad design problem and normalization may be required. – Madhur Bhaiya Nov 09 '18 at 14:43
  • Each record has a Unique_ID, Username, T1_Choice, T2_Choice, DB_Year. The username is the user's unique identifier and then the Choice_ID goes into each of the Tn_Choice columns. The database has been thoroughly checked and it is normalised. – Toby Dixon Smith Nov 09 '18 at 14:48
  • This requires aggregation. You will need to use `Group by`, as you want one row per student. – Madhur Bhaiya Nov 09 '18 at 14:52
  • 1
    Try to provide some sample data and expected output. Refer this link on how to frame the question: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/q/333952/2469308) – Madhur Bhaiya Nov 09 '18 at 14:57
  • 1
    Complete export is not needed. Try to reduce it some 2-3 rows, covering a particular student case completely. Based on that, please prepare a [DB Fiddle](https://www.db-fiddle.com/) link and give that. – Madhur Bhaiya Nov 09 '18 at 15:18
  • https://www.db-fiddle.com/f/eGeatQzZMPygxPD7E6usEa/1. So If you were to correctly code the statement you should get the name to be Dixon Smith, Tobias A and sports of Badminton, Fives then Cricket @MadhurBhaiya – Toby Dixon Smith Nov 09 '18 at 15:30
  • Possible duplicate of [How to get matching data from another SQL table for two different columns: Inner Join and/or Union?](https://stackoverflow.com/questions/27682228/how-to-get-matching-data-from-another-sql-table-for-two-different-columns-inner) – philipxy Nov 09 '18 at 19:11
  • Please clarify via post edits, not comments. Please include all relevant code in your question, not just at the linked fiddle. PS This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Nov 09 '18 at 19:14

1 Answers1

1

The solution was that you could call JOIN to a table multiple times. Because Choice_ID from Choices needs to be linked to different term choices in the Student_Choices table. So you need to JOIN the Choices table multiple times with different aliases and for each instance, you need to JOIN the Sports table with a different alias. My solution is:

("SELECT st.Name AS student, T1.Name AS T1, T2.Name AS T2, T3.Name AS T3
              From Students AS st INNER JOIN Student_Choices AS sc
              ON st.Username = sc.Username
              INNER JOIN Choices AS c1
              ON sc.T1_Choice = c1.Choice_ID
              INNER JOIN Sports AS T1
              ON c1.Sport_ID = T1.Sport_ID
              INNER JOIN Choices AS c2
              ON sc.T2_Choice = c2.Choice_ID
              INNER JOIN Sports AS T2
              ON c2.Sport_ID = T2.Sport_ID
              INNER JOIN Choices AS c3
              ON sc.T3_Choice = c3.Choice_ID
              INNER JOIN Sports AS T3
              ON c3.Sport_ID = T3.Sport_ID
              ");
Toby Dixon Smith
  • 45
  • 9
  • 1
    Yes. correct answer +1. However, when you are going to have multiple joins like these for similar columns; it generally implies that normalizing the data is way to go. Will ask you to google up Entity Attribute Value pattern. – Madhur Bhaiya Nov 10 '18 at 04:50