MySQL多表查询：获取班级ID10的全部公共及专属费用名称

Alright, let's break this down properly. You need all fees for class ID 10: every public fee (status=1) plus the exclusive fees tied directly to that class. Here are two reliable ways to write this query based on your table setup:

Method 1: Use UNION to Combine Two Results #

This approach splits the problem into two clear parts—fetching public fees, then fetching class-specific fees—and merges them. UNION automatically removes any duplicate fee names (though you probably won't have duplicates here, it's a safe default):

-- Fetch all public fees (applicable to every class)
SELECT fee_name
FROM Fee_Table
WHERE status = 1

UNION

-- Fetch exclusive fees linked specifically to class ID 10
SELECT ft.fee_name
FROM Fee_Table ft
INNER JOIN Class_Table ct 
  ON ft.fee_id = ct.fee_id  -- Assuming fee_id is the linking column between tables
WHERE ft.status = 2 
  AND ct.class_id = 10;

Method 2: Single Query with LEFT JOIN and Filter Conditions #

If you prefer a single query, this uses a LEFT JOIN to connect the fee and class tables, then filters for either public fees or exclusive fees tied to class 10. We use DISTINCT to avoid duplicate fee names if there are multiple entries in Class_Table for the same fee and class:

SELECT DISTINCT ft.fee_name
FROM Fee_Table ft
LEFT JOIN Class_Table ct 
  ON ft.fee_id = ct.fee_id
WHERE 
  -- Include all public fees
  ft.status = 1 
  -- OR include exclusive fees that are linked to class ID 10
  OR (ft.status = 2 AND ct.class_id = 10);

Quick Notes: #

• Double-check that fee_id is the correct column linking Fee_Table and Class_Table—replace it with your actual linking column if it's named differently.
• If you're sure there are no duplicate fee names, you can omit DISTINCT (in Method 2) or use UNION ALL (in Method 1) for a minor performance boost.
• Test each part of the query separately first (e.g., run just the public fees query) to verify it returns the expected data before combining.

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