如何从3表中获取列。？ [英] How to Get column From 3 table.?

问题描述

我有桌子：

i have table:

customer:cid(PK),custid,company,product,productdate,total,note
paymentData:pid(PK),pcid(FK),custid,cleint,total,receiveamt,restamt
Paymet:id(PK),pid(FK),custid,client,product,receiveamt,restamt

> custid在所有表中相同

>产品可以在paymentdata中付款，付款

1）我想要客户的所有数据

2）我想要数据

custid，company，product，productdate，total，notem，reciveamt，restamt



- 我尝试

>custid same in all table
>product can be diffrent in paymentdata,payment
1)i want all data of customer
2)i want data
custid,company,product,productdate,total,notem,reciveamt,restamt

--i try

SELECT CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total,CM.notem
,PM.reciveamt,PM.restamt
FROM
	customermaintenance AS CM
	
	LEFT JOIN paymentdata AS PD ON PD.pcid=CM.cid
	INNER JOIN payment AS PM ON PD.pid=PM.pid

- 但它没有从客户那里获得所有数据

- ????

--but it not get all data from customer
--????

推荐答案

您的查询只会返回客户至少有一张付款记录的客户维护记录。



尝试

Your query will only return customermaintenance records where the customer has at least one payment record.

Try

SELECT CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total,CM.notem
,PM.reciveamt,PM.restamt
FROM
    customermaintenance AS CM

    LEFT OUTER JOIN paymentdata AS PD ON PD.pcid=CM.cid
    LEFT OUTER JOIN payment AS PM ON PD.pid=PM.pid

[对OP评论的编辑回复]

不完全清楚你要做什么，但以下查询将根据日期的当前总数将记录插入到DueMaintenance表中...

Not entirely clear what you are trying to do, but the following query will insert records into the DueMaintenance table based on current totals by date...

INSERT INTO DueMaintenance 
SELECT CM.custclientid,CM.company,CM.product,CM.producttakendate,SUM(CM.total) as total,CM.notem,SUM(PM.reciveamt) as reciveamt,SUM(PM.restamt) as restamt
FROM
    customermaintenance AS CM
    LEFT OUTER JOIN paymentdata AS PD ON PD.pcid=CM.cid
    LEFT OUTER JOIN payment AS PM ON PD.pid=PM.pid
GROUP BY CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.notem

这篇关于如何从3表中获取列。？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！