MySQL 多重内连接？

Question

我有三个表...

create table customers (
customerID integer unsigned not null auto_increment,
customername varchar(45) not null,
address varchar(45) not null,
city varchar(45) not null,
state varchar(2) not null,
zip mediumint(5) zerofill not null,
primary key (customerID)
);

create table products (
prodID integer unsigned not null auto_increment,
prodname varchar(45) not null,
prodcat integer unsigned,
proddesc varchar(75) not null,
price float (8,2),
qoh integer not null,
prodiconurl varchar(45),
primary key (prodID),
constraint FK_CAT foreign key FK_CAT (prodcat)
    references categories (catID)
    on delete restrict
    on update restrict
);

create table SHOPPING_CART (
CustomerID INTEGER UNSIGNED NOT NULL,
ProductID INTEGER UNSIGNED NOT NULL,
ProdQnty INTEGER UNSIGNED NOT NULL,
Primary Key (CustomerID, ProductID),
CONSTRAINT Fk_Cust Foreign Key Fk_Cust (CustomerID)
    References Customers (CustID)
    On Delete restrict
    On Update restrict, 
CONSTRAINT Fk_Prod Foreign Key Fk_Prod (ProductID)
    References Products (ProdID)
    On Delete restrict
    On Update restrict
);

table 很好。我遇到的问题是我的 SHOPPING_CART 的 SELECT 语句。如果我插入以下代码。

insert into SHOPPING_CART
Values (
1, 4, 2
);
insert into SHOPPING_CART
values (
1, 9, 1
);

我的内部联接无法根据购物车表中的值查找 CustID、从 Customers 表返回 FirstName 和 LastName 字段并从 Products 表返回产品名称。

这是我的最终目标。

SELECT 
Customers.FirstName,
Customers.LastName,
Products.Name,
SHOPPING_CART.ProdQnty
FROM SHOPPING_CART
inner join Customers on SHOPPING_CART.CustomerID = Customers.CustID
where Customers.CustID = SHOPPING_CART.CustomerID
inner join Products on SHOPPING_CART.ProductID = Products.ProdID
where Products.ProdID = SHOPPING_CART.ProductID;

当第二个内部连接是语句的一部分时，我得到一个语句错误，而且该语句在第二个内部连接上有一个红色的 x。

Answer 1

只有 1 个 where 语句是必需的。第二个 where 语句将位于“AND”语句之下。注意:代码是 ANSI-92 之前的代码。如何使用 ANSI-92 的说明遵循代码示例。

SELECT 
Customers.FirstName,
Customers.LastName,
Products.Name,
SHOPPING_CART.ProdQnty
FROM SHOPPING_CART
WHERE Customers.CustID = SHOPPING_CART.CustomerID
AND Products.ProdID = SHOPPING_CART.ProductID;

历史:

在 ANSI 92 之前，where 语句中的连接很常见。标准化后，连接从 where 语句移动到 from 语句。 Where 语句用于提供额外的过滤功能，例如其中状态 = '打开'。以下摘录解释了 ANSI-92 连接语法。

The ANSI Join Syntax Before the ANSI SQL-92 standard introduced the new join syntax, relations (tables, views, etc.) were named in the FROM clause, separated by commas. Join conditions were specified in the WHERE clause:

=> SELECT * FROM T1, T2 WHERE T1.id = T2.id; The ANSI SQL-92 standard provided more specific join syntax, with join conditions named in the ON clause:

=> SELECT * FROM T1 [ INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER | NATURAL | CROSS ] JOIN T2 ON T1.id = T2.id See SQL-99 ANSI syntax at BNF Grammar for SQL-99 for additional details.

Advantages:

SQL-92 outer join syntax is portable across databases; the older syntax was not consistent between databases. SQL-92 syntax provides greater control over whether predicates are to be evaluated during or after outer joins. This was also not consistent between databases when using the older syntax. See "Join Conditions vs. Filter Conditions" below. SQL-92 syntax eliminates ambiguity in the order of evaluating the joins, in cases where more than two tables are joined with outer joins. Union joins can be expressed using the SQL-92 syntax, but not in the older syntax.

包含 ANSI-92 的更新查询:

SELECT 
Customers.FirstName,
Customers.LastName,
Products.Name,
SHOPPING_CART.ProdQnty
FROM SHOPPING_CART
inner join Customers on SHOPPING_CART.CustomerID = Customers.CustID
inner join Products on SHOPPING_CART.ProductID = Products.ProdID