Select Join Table A and Table B while displaying Table B items based on Table A's foreign key

Question

I'm in the midst of writing an SQL statement and I have a problem joining both tables (let's call it Table Enrollment & Product) based on a foreign key column from table Enrollment's column StudentID. The StudentID is read from a variable $StudentID.

Enrollment table

+--------------+-----------+-----------+

| EnrollmentID | StudentID | ProductID |

+--------------+-----------+-----------+

| 1            | 5         | 1         |

| 2            | 5         | 2         |

|3             | 7         | 2         |

|4             | 7         | 3         |

|5             | 9         | 5         |

+--------------+-----------+-----------+

Product Table

+-----------+-------------+------------+

| ProductID | ProductName | ProductDate|

+-----------+-------------+------------+

|1          | ProductA    | 1/1/2017   |

|2          | ProductB    | 1/2/2017   |

|3          | ProductC    | 1/3/2017   |

|4          | ProductD    | 1/4/2017   |

+-----------+-------------+------------+

This is the SQL statement so far i've come up and having trouble with:

"SELECT product.ProductName, product.ProductDate
                                FROM enrollment WHERE StudentID = '$StudentID'
                                RIGHT JOIN product ON enrollment.productid = product.productid";

If $StudentID is 5. I want it to display all items from ProductID that is associated with StudentID 5. Which in this case, Product A and Product B will be the output.

It sounds as though `Enrollment` is a [**bridging table**](https://en.wikipedia.org/wiki/Associative_entity), and that `StudentID` is coming from a **third** table (`Student`?). You'll also need to join that table as well :) — Obsidian Age, Jun 06 '17 at 03:11
Yes it's from a third table. But no columns are used from that table but only the `StudentID`. I'm not familiar with joining three tables. — Anonymous Vagrant, Jun 06 '17 at 03:15
It doesn't matter if you're not **displaying** the data from the third table, you're still **using** the ID to 'associate' the two tables together. To join the three tables together, you essentially just need to tack another `JOIN` at the end (between `Enrollment` and `Student`). Here's [**some examples**](https://stackoverflow.com/questions/3709560/mysql-join-three-tables) to help with that :) — Obsidian Age, Jun 06 '17 at 03:18
Doesn't seem to work. This is what i wrote `SELECT product.ProductName, product.ProductDate FROM enrollment WHERE StudentID = '$StudentID' JOIN student ON enrollment.studentid = student.studentid RIGHT JOIN product ON enrollment.productid = product.productid"` — Anonymous Vagrant, Jun 06 '17 at 03:23

score 1 · Answer 1 · answered Jun 06 '17 at 03:26

1

DECLARE @StudentID INT = 5;

SELECT p.productname, p.productdate FROM Enrollment e RIGHT JOIN producttable p ON e.productid = p.productid WHERE e.studentid = @StudentID

answered Jun 06 '17 at 03:26

Sujeet Kumar

56
2

score 1 · Accepted Answer · answered Jun 06 '17 at 04:45

By reading your sql code, I assume you use PHP to generate sql query. Please use {$variable} when ever you want to include variable value inside string in PHP. And you have to join the table first and then apply condition. Use the below query,

"SELECT product.ProductName, product.ProductDate FROM enrollment inner join student ON enrollment.studentid = student.studentid  RIGHT JOIN product ON enrollment.productid = product.productid WHERE enrollment.StudentID = '{$StudentID}' "

Select Join Table A and Table B while displaying Table B items based on Table A's foreign key

2 Answers2