如何内连接3张表？创建含Inner Join的视图遇问题求助

Hey there! Let's tackle your two SQL questions one by one, using your provided table structures as a guide.

Since Assign acts as the junction table linking Projects and Employees, you’ll need to chain inner joins using their shared foreign keys:

• Join Projects with Assign on ProjID
• Join the resulting dataset with Employees on EmpID

Here’s a clean inner join query that pulls related data across all three tables:

SELECT
    p.ProjID,
    p.ProjName,
    e.EmpName,
    a.Hours,
    -- Add other fields you need from any table here
    a.Status,
    e.City
FROM Projects p
INNER JOIN Assign a 
    ON p.ProjID = a.ProjID
INNER JOIN Employees e 
    ON a.EmpID = e.EmpID;

• Table aliases (p, a, e) keep the query concise and avoid ambiguous column names.
• INNER JOIN ensures only records with matching keys across all joined tables are returned—so no orphaned project/employee assignments will appear in results.

The duplicate results you’re seeing almost certainly stem from multiple entries in the Assign table for the same ProjID + EmpID pair (for example, an employee logging hours on the same project on different dates, or with different Status values). Let’s build the correct view and resolve the duplicates based on your requirements.

Option 1: Remove Exact Duplicate Rows #

If you just want to eliminate identical rows where all selected fields (ProjID, ProjName, EmpName, Hours) are repeated, use DISTINCT:

CREATE VIEW View1 AS
SELECT DISTINCT
    p.ProjID,
    p.ProjName,
    e.EmpName,
    a.Hours
FROM Projects p
INNER JOIN Assign a 
    ON p.ProjID = a.ProjID
INNER JOIN Employees e 
    ON a.EmpID = e.EmpID
WHERE p.ProjID LIKE 'N%'; -- Filters projects starting with "N"

Option 2: Aggregate Hours for Repeated Entries #

If an employee has multiple time entries for the same project, you’ll likely want to summarize those hours instead of just removing duplicates. For example, to get total hours per employee per project:

CREATE VIEW View1 AS
SELECT
    p.ProjID,
    p.ProjName,
    e.EmpName,
    SUM(a.Hours) AS TotalHours -- Use AVG(a.Hours) for average hours instead
FROM Projects p
INNER JOIN Assign a 
    ON p.ProjID = a.ProjID
INNER JOIN Employees e 
    ON a.EmpID = e.EmpID
WHERE p.ProjID LIKE 'N%'
GROUP BY p.ProjID, p.ProjName, e.EmpName; -- Group by all non-aggregated fields

Why Duplicates Occur #

The Assign table likely contains multiple rows for the same ProjID and EmpID (due to varying Date or Status values). When you join tables, each of these rows creates a separate entry in your result set. Choose between DISTINCT and aggregation based on your needs: do you want to see individual time entries, or a summarized overview?

内容的提问来源于stack exchange，提问作者J. Wolf