如何从三个表中选择数据？ [英] How to Select Data From three Table?

问题描述

我有桌子：



客户：cid（PK），custid，公司，产品，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



- 我试试

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付款AS PM ON PD.pid = PM.pid







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

- ????

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 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
--????

推荐答案

看到pclientid是Pk支付和客户维护的Fk并尝试执行此命令。

Hi,
see that pclientid is Pk payment and Fk of customermaintenance and try executing this command.

SELECT CM.custclientid,CM.company,CM.product,CM.producttakendate,CM.total,CM.notem
,PM.reciveamt,PM.restamt
FROM	
payment PM, customermaintenance CM
WHERE PM.pclientid=CM.custclientid

您将获得多行custid，因为客户可以进行多次付款，这意味着客户客户表中的行可以在付款表中有多行。



您需要检查您的要求究竟是什么。一个简单的查询可能是返回客户的最新付款，例如

You are getting multiple rows of a custid because a customer can have multiple payments that means a customer row in Customer table can have multiple rows in payment table.

You need to check your requirements what exactly you want. A simple query could be to return a customer's latest payment which is like

SELECT C.custid,C.company,C.product,C.producttakendate,C.total,C.note,PM.reciveamt,PM.restamt
FROM payment AS PM
INNER JOIN (SELECT C.custid,MAX(Payment_Id) MAX_PAYMENT_ID
FROM customer  AS C
INNER JOIN payment AS PM ON PM.pclientid=C.custid
) T ON PM.MAX_PAYMENT_ID = T.MAX_PAYMENT_ID AND PM.pclientid = T.custid

你提到过的一部分作为客户，但后来您引用了另一个表CustomerMaintenance？

A part from that you have mentioned initially as Customer but later you have referenced a different table CustomerMaintenance?

这篇关于如何从三个表中选择数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！