Home/ Questions/Q 12341
Next
In Process

askthedev.com Latest Questions

Asked: 2024-09-26T18:03:12+05:30 In:

How can I perform an inner join involving three different tables in SQL? I’m looking for a clear example that illustrates how to combine data from these tables based on specific matching criteria.

I’ve been diving into SQL lately, and I came across a bit of a puzzler that maybe someone here can help me out with. So, picture this: I’ve got three tables in my database—let’s call them `Students`, `Courses`, and `Enrollments`.

The `Students` table has some basic info about each student, including the columns `student_id`, `name`, and `age`. The `Courses` table shows the details about classes available at our institution, with columns like `course_id`, `course_name`, and `credits`. Finally, the `Enrollments` table is where the fun starts! This table tracks which students are enrolled in which courses and has `enrollment_id`, `student_id`, and `course_id` columns.

Here’s the breakdown of what I want to achieve: I’d like to compile a list of all students along with the names of the courses they are enrolled in and the credits for those courses. The catch is that I want to ensure that I only get results for students who are actually enrolled in courses, so we need to carefully join these tables.

I’m imagining the result would have columns like `student_name`, `course_name`, and `credits`, but I’m a little stuck on how to properly structure the SQL query. My first thought was something along the lines of using inner joins, but I’m not entirely sure how to pull it all together.

If someone could help me understand how to properly write this SQL statement to get the inner join right, I’d really appreciate it! Specifically, I’m struggling with how to link these tables correctly based on the foreign keys—like `student_id` in both `Students` and `Enrollments`, and `course_id` in both `Courses` and `Enrollments`.

Any guidance or a sample query would be fantastic! Thanks in advance for your help!

Share

    Leave an answer

    You must login to add an answer.

    Need An Account,

    2 Answers

    1. Hey! So, I totally get where you’re coming from—SQL can be tricky, especially when it comes to joining tables!

      To get the list of all students with the names of the courses they’re enrolled in and the credits for those courses, you’ll want to use INNER JOIN to connect the three tables based on the foreign keys you mentioned.

      Here’s a simple SQL query that should do the trick:

              
SELECT 
            s.name AS student_name, 
            c.course_name, 
            c.credits
FROM 
            Students s
INNER JOIN 
            Enrollments e ON s.student_id = e.student_id
INNER JOIN 
            Courses c ON e.course_id = c.course_id;

      So, what this does is:

      • Selects the name from the Students table, which is aliased as s.
      • Joins the Enrollments table (aliased as e) on student_id so you get the enrollments specific to each student.
      • Then it connects the Courses table (aliased as c) using course_id so you can grab the course names and credits.

      Make sure to alias your tables like I did here—it makes it cleaner and easier to read. If a student isn’t enrolled in any course, they won’t show up in your results because of the INNER JOIN. Hope this helps you get what you need!

    2. To achieve your goal of compiling a list of students along with the names of the courses they are enrolled in and the associated credits, you can use SQL inner joins to connect the three tables: `Students`, `Courses`, and `Enrollments`. The `Enrollments` table acts as the bridge to connect students to their respective courses. Here’s how you can structure your SQL query:

      SELECT s.name AS student_name, 
       c.course_name, 
       c.credits 
FROM Students s 
INNER JOIN Enrollments e ON s.student_id = e.student_id 
INNER JOIN Courses c ON e.course_id = c.course_id;

      This query will return a result set that includes the student’s name (`student_name`), the corresponding course name (`course_name`), and the number of credits associated with each course. The inner joins ensure that only students who are enrolled in at least one course will appear in your results. Thus, it links `Students` and `Enrollments` on the `student_id`, and `Enrollments` and `Courses` on the `course_id`, yielding the desired information.

    Related Questions