如何加入3张桌子 [英] How to join 3 tables

问题描述

大家好，

我有三张桌子.每个表中的项目如下:

Hello every member,

I have three tables. Items in each table as follow:

1-product (id, productcode, name, packagesize, balance, groupid)<br />
2-productgroup (groupid, groupname)<br />
3-purchaseorder ( id, ponumber, productcode, podate, outstandingbalance, balanceorder)

我有连接两个表(按总和列的产品和采购订单列(未清余额)，其中以下项作为代码重复了:

I have Join two table (product and purchaseorder by sum column(outstandingbalance) which item that duplicated as code below:

DIM da AS NEW OLEDB.OLEDBDATAADAPTER("SELECT P.productid, P.fullcode, P.productname, P.packagesize, P.pcsperctn , sum(PO.outstandingbalance) AS [PurchaseOutstanding], P.balanceinstock, P.supplier from product P left outer join purchaseorder PO on P.fullcode=PO.productcode group by P.productid, P.fullcode, PO.productcode, P.productname, P.packagesize, P.pcsperctn,P.balanceinstock, P.supplier", cn)

但我尝试将三个结合在一起(3个表)，如下所示:

but I try to join three together ( 3 table) as below:

DIM da AS NEW OLEDB.OLEDBDATAADAPTER("SELECT P.productid, P.fullcode, P.productname , P.packagesize, P.pcsperctn , sum(PO.outstandingbalance) AS [purchase], P.balanceinstock, P.supplier, G.groupname from productgroup G, product P left outer join purchaseorder PO on P.fullcode=PO.productcode where P.groupid=G.groupid group by P.productid, P.fullcode, PO.productcode,P.productname,P.packagesize, P.pcsperctn,P.balanceinstock, P.supplier, G.productname", cn)

然后运行了该应用程序，因此出现类似不支持Join表达式"的错误.

谢谢&问候

VISAL

and then I have run the application so I get error like this " Join expression not supported. "

Thank & Regards

VISAL

推荐答案

from productgroup G, product P left outer join purchaseorder PO on P.fullcode=PO.productcode

您尚未加入productgroup.

请参考 http://msdn.microsoft.com/en-us/library /aa213227(v=sql.80).aspx [ ^ ]

You have not joined productgroup.

Refer to http://msdn.microsoft.com/en-us/library/aa213227(v=sql.80).aspx[^]

正如Prera​​k指出的，问题出在联接中.

As Prerak indicated the problem is in the join.

SELECT
    P.productid,
    P.fullcode,
    P.productname ,
    P.packagesize,
    P.pcsperctn ,
    sum(PO.outstandingbalance) AS [purchase],
    P.balanceinstock,
    P.supplier,
    G.groupname
FROM
    product P
    INNER JOIN productgroup G ON (P.groupid=G.groupid )
    LEFT OUTER JOIN purchaseorder PO ON (P.fullcode=PO.productcode)
GROUP BY
    P.productid,
    P.fullcode,
    PO.productcode,
    P.productname,
    P.packagesize,
    P.pcsperctn,
    P.balanceinstock,
    P.supplier,
    G.productname

尝试
联接三个或更多表[

Try
Joining Three or More Tables[^]

这篇关于如何加入3张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！