mysql - 为什么当我内部联接所有三个表时，我得到重复的数据值作为记录？

INSERT INTO Supplier(Name, City, PhoneNumber, Date, ZipCode)
VALUES ('Sandra Auto Parts', 'Akron', '330-217-1263', '2015-01-22', '44303');

INSERT INTO Supplier(Name, City, PhoneNumber, Date, ZipCode)
VALUES ('Justin Auto Mechanics Parts', 'Cleveland', '216-227-2134', '2014-02-01', '44102');

INSERT INTO Supplier(Name, City, PhoneNumber, Date, ZipCode)
VALUES ('Mark Diesel Parts', 'Euclid', '216-223-4133', '2013-01-03', '44103');

SELECT * FROM supplier;



INSERT INTO Parts(PartName, DatePartCreated)
VALUES ('Hood', '2015-03-21');

INSERT INTO Parts(PartName, DatePartCreated)
VALUES('Rim', '2016-02-22');

INSERT INTO Parts(PartName, DatePartCreated)
VALUES ('Bumper', '2016-01-24');

SELECT *FROM Parts;


INSERT INTO Supplies(Supplier_SupplierID, Parts_PartID, Cost) VALUES (1, 1, 10);

INSERT INTO Supplies(Supplier_SupplierID, Parts_PartID, Cost) VALUES (2, 2, 20);

INSERT INTO Supplies(Supplier_SupplierID, Parts_PartID, Cost) VALUES (3, 3, 30);

SELECT *FROM Supplies;

我的SQL：

SELECT
 supplier.Name AS 'Supplier Name',
 parts.PartName AS 'Part Name',
 Cost
FROM
Supplier
INNER JOIN
Parts ON SupplierID = PartID
INNER JOIN
Supplies ON  Parts_PartID= Supplier_SupplierID
ORDER BY supplier.Name, parts.PartName;

我有三个表的内部联接，分别是零件表，供应商表和供应表。我的问题是为什么当我内部连接这三个表时，它们重复记录不止一次。它只能是一个记录存储一次，不能多次。可以提供任何建议吗？这是我的SQL代码和问题的屏幕截图。 Here is screenshot of my problem

最佳答案

您联接表不正确。您想将供应商表和零件表与供应表连接在一起，而不是彼此连接。

我想你想要这个：

select supplier.name as 'Supplier Name',
    parts.PartName as 'Part Name',
    Cost
from Supplies
join Parts
    on Parts_PartID = PartId
join Supplier
    on Supplier_SupplierID = SupplierId
order by supplier.name,
    parts.PartName;

Demo

关于mysql - 为什么当我内部联接所有三个表时，我得到重复的数据值作为记录？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42612361/