SQL Developer技术问询：如何查询Brown教授关联的学生/课程ID与名称

Ah, I get it—you want to list both the courses taught by Professor Brown and the students enrolled in those courses, all under the same two columns ("Id" and "Student or course"). The problem with your current query is that you're joining all the tables together, which mixes student and course data into the same row, leading to duplicate columns.

Instead of joining everything in one SELECT, we need to use UNION (or UNION ALL) to combine two separate result sets: one for courses, and one for students. Here's how to do it:

Step 1: Query Courses Taught by Prof. Brown #

First, let's get all the courses associated with Professor Brown:

SELECT 
  c.course_no AS "Id", 
  c.course_name AS "Student or course"
FROM professors f
JOIN course c ON c.prof_id = f.prof_id
WHERE f.f_name = 'Brown';

Step 2: Query Students Enrolled in Those Courses #

Next, get all students who are taking courses taught by Prof. Brown (note: I'm assuming there's a registration table that links students to courses—you used r.course_no in your original query, so I'll use registrations as the table name here):

SELECT 
  s.s_id AS "Id", 
  s.s_name AS "Student or course"
FROM professors f
JOIN course c ON c.prof_id = f.prof_id
JOIN registrations r ON r.course_no = c.course_no
JOIN students s ON s.s_id = r.s_id
WHERE f.f_name = 'Brown';

Step 3: Combine the Two Results with UNION ALL #

Now, merge these two results into a single set using UNION ALL (use UNION instead if you want to remove duplicate records, though UNION ALL is faster since it doesn't check for duplicates):

-- Courses taught by Prof. Brown
SELECT 
  c.course_no AS "Id", 
  c.course_name AS "Student or course"
FROM professors f
JOIN course c ON c.prof_id = f.prof_id
WHERE f.f_name = 'Brown'

UNION ALL

-- Students enrolled in those courses
SELECT 
  s.s_id AS "Id", 
  s.s_name AS "Student or course"
FROM professors f
JOIN course c ON c.prof_id = f.prof_id
JOIN registrations r ON r.course_no = c.course_no
JOIN students s ON s.s_id = r.s_id
WHERE f.f_name = 'Brown';

Why Your Previous Attempts Didn't Work #

• Case Statements: These work for choosing values within the same row, not for combining different types of records into separate rows. For example, you couldn't use a Case to show a course ID in one row and a student ID in another—Case only affects what's displayed in a single row.
• WITH-AS: This is great for creating temporary result sets, but you still need to combine those sets with UNION to get the output you want. You probably tried to join the CTEs instead of unioning them, which led to the same duplicate column issue.

Quick Notes #

• If your registration table has a different name (like student_course), just replace registrations with the correct table name.
• Double-check that the data types of course_no and s_id are compatible (both numeric or both string) since UNION requires matching column types.

内容的提问来源于stack exchange，提问作者Mihai Parlea