mysql - 内部联接行不受影响

Question

当我尝试使用此代码在我的表 salesfacts 中插入数据时，它说 0 行受到影响，但是当我删除插入时，仅执行选择，它显示我的 salesfacts 表。任何人都可以帮我解决这个问题。

truncate table SalesFacts

INSERT INTO Assignment1SS..SalesFacts
(ProductCode, CustomerNumber, EmployeeNumber, officeCode, buyPrice, MSRP, quantityOrdered) 

SELECT 

p.ProductCode, 
c.CustomerNumber, 
e.EmployeeNumber, 
y.officeCode, 
p.buyPrice, 
p.MSRP, 
od.quantityOrdered

FROM
Assignemnt1..[OrderDetails] od INNER JOIN Assignemnt1..[Orders] o
ON od.orderNumber = o.orderNumber

INNER JOIN Assignment1SS..[ProductSS] p 
ON od.productCode = p.productCode

INNER JOIN Assignment1SS..[CustomersSS] c
ON od.OrderNumber = c.CustomerNumber

INNER JOIN Assignment1SS..[EmployeeSS] e
ON c.SalesRepEmployeeNumber = e.EmployeeNumber

INNER JOIN

(select e.[EmployeeNumber], y.[officeCode]
from Assignemnt1..[Employees] e
inner join Assignemnt1..[Offices] y
on e.officeCode = y.officeCode
group by e.[EmployeeNumber],y.[officeCode]) y

on c.salesRepEmployeeNumber = y.EmployeeNumber 

CustomersSS 表

    Create Table CustomersSS (
CustomerNumber int NOT NULL,
CustomerName varchar (50) NOT NULL,
ContactLastName varchar (50) NOT NULL,
ContactFirstName varchar (50) NOT NULL,
Phone varchar (50) NOT NULL,
AddressLine1 varchar (50) NOT NULL,
AddressLine2 varchar (50) NULL,
City varchar (50) NOT NULL,
State varchar (50) NULL,
PostalCode varchar (15) NULL,
Country varchar (50) NOT NULL,
SalesRepEmployeeNumber int NULL,
CreditLimit money NOT NULL,
PRIMARY KEY (CustomerNumber));

Answer 1

您的查询有误，请使用以下查询。

Your fault is 

    INNER JOIN Assignment1SS..[CustomersSS] c
    ON od.OrderNumber = c.CustomerNumber

Change query :

truncate table SalesFacts
INSERT INTO Assignment1SS..SalesFacts
(ProductCode, CustomerNumber, EmployeeNumber, officeCode, buyPrice, MSRP, quantityOrdered) 

SELECT 

p.ProductCode, 
c.CustomerNumber, 
e.EmployeeNumber, 
y.officeCode, 
p.buyPrice, 
p.MSRP, 
od.quantityOrdered FROM Assignemnt1..[OrderDetails] od 

INNER JOIN Assignemnt1..[Orders] o ON od.orderNumber = o.orderNumber
INNER JOIN Assignment1SS..[ProductSS] p ON od.productCode = p.productCode
INNER JOIN Assignment1SS..[CustomersSS] c ON od.CustomerNumber = c.CustomerNumber
INNER JOIN Assignment1SS..[EmployeeSS] e ON c.SalesRepEmployeeNumber = e.EmployeeNumber
INNER JOIN (select e.[EmployeeNumber], y.[officeCode]
                from Assignemnt1..[Employees] e
                inner join Assignemnt1..[Offices] y
                on e.officeCode = y.officeCode
                group by e.[EmployeeNumber],y.[officeCode]) y 
                    on c.salesRepEmployeeNumber = y.EmployeeNumber