我不知道如何问这个问题,甚至不知道如何命名它,所以如果以前有人问过,我很抱歉。
我创建了一个联接,用于检索我需要的所有信息。 有点。
它从客户表中返回客户 ID,而不是客户的详细信息:
SELECT o.orderTotal, o.orderID, oc.productID, oc.quantity, oc.price_per, co.customerID
FROM tblOrders o
INNER JOIN tblOrderContents oc ON o.orderID = oc.orderID
INNER JOIN tblCustomerOrders co ON o.orderID = co.orderID
我的客户表是:
**tblCustomer**
customerID
customerName etc...
如何使此查询在同一 SQL 查询中返回实际的客户信息,例如姓名、地址等,我甚至可以这样做吗?
只需用tblCustomer
再做一次join
SELECT o.orderTotal, o.orderID, oc.productID, oc.quantity, oc.price_per, co.customerID, c.customerName
FROM tblOrders o
INNER JOIN tblOrderContents oc ON o.orderID = oc.orderID
INNER JOIN tblCustomerOrders co ON o.orderID = co.orderID
INNER JOIN tblCustomer c on co.customerID = c.customerID
通过联接客户表,您可以访问任何字段。因此,只需在 SELECT 子句中添加所需的字段并附加连接:
SELECT
o.orderTotal,
o.orderID,
oc.productID,
oc.quantity,
oc.price_per,
co.customerID,
cu.customerName
FROM
tblOrders o
INNER JOIN
tblOrderContents oc ON o.orderID = oc.orderID
INNER JOIN
tblCustomerOrders co ON o.orderID = co.orderID
INNER JOIN
tblCustomer cu ON cu.customerID = co.customerID
如果客户的信息在 tblCustomer 中,则可以将查询结果与此表连接
。select I.*
from
(SELECT o.orderTotal, o.orderID, oc.productID, oc.quantity, oc.price_per, co.customerID
FROM tblOrders o
INNER JOIN tblOrderContents oc ON o.orderID = oc.orderID
INNER JOIN tblCustomerOrders co ON o.orderID = co.orderID) as O left join tblCustomer I on O.customerId=I.customerId