在sql数据库中加入问题
我有三张桌子.....
1. ClientDetailTable（clientId作为主键）
2. PaymentDetailTable（paymentId作为主键）（clientId作为外键）密钥）
3. AddressDetailTable（AddId作为主键）（clientId作为外键）
现在我想选择ClientDetailTable中存在的所有客户记录及其地址来自
AddressDetailTable。我没有问题（每个客户端的记录在AddressDetailTable和ClientDetailTable中只存在一次），但是当我尝试从PaymentDetailTable加入corressponds记录时，我遇到了问题。实际上在PaymentDetailTable记录中存在多次（paymentId设置为身份（1,1））现在我想从PaymentDetailTable中选择记录，该记录对该客户具有最大的paymentId....
，这样我想加入这三张桌子......请给出建议bros ......
i希望你明白这个问题，等待响应......
i have three tables .....
1. ClientDetailTable(clientId as primary key )
2. PaymentDetailTable (paymentId as primary key ) (clientId as foreign key )
3. AddressDetailTable (AddId as primary key) (clientId as foreign key)
now i want to select all clients record which exist in "ClientDetailTable" and their addresses from
"AddressDetailTable " . i have no problem in this (record for each client exist only once in both "AddressDetailTable " and "ClientDetailTable" ) , but when i am trying to join corressponds record from "PaymentDetailTable " i have arise a problem . Actually in "PaymentDetailTable " record exist more than once (paymentId is set identity(1,1)) now i want to select record from "PaymentDetailTable " which has maximum "paymentId " for that client ....
and in this way i want to join these three tables ....pls give suggestion bros......
i hope u understand the problem , waiting for response ......
这可能有帮助
1.你可以分组PaymentDetailTable中的ClientID并从PaymentDetailTable获取Count（paymentId）
2.获取结果集的最大值并加入其他表
This may help
1.You can group by the ClientID in PaymentDetailTable and get the Count(paymentId) from PaymentDetailTable
2.take the max of the result set and join with the Other tables
SELECT ClientDetailTable。 clientId，ClientDetailTable.clientName，AddressDetailTable.Address，PaymentDetailTable.paymentId
FROM ClientDetailTable INNER JOIN
AddressDetailTable ON ClientDetailTable.clientId = AddressDetailTable.clientId INNER JOIN
PaymentDetailTable ON ClientDetailTable.clientId = PaymentDetailTable.clientId
AND PaymentDetailTable.paymentId IN（SELECT MAX（paymentId）FROM PaymentDetailTable GROUP BY clientId）
SELECT ClientDetailTable.clientId, ClientDetailTable.clientName, AddressDetailTable.Address,PaymentDetailTable.paymentId
FROM ClientDetailTable INNER JOIN
AddressDetailTable ON ClientDetailTable.clientId = AddressDetailTable.clientId INNER JOIN
PaymentDetailTable ON ClientDetailTable.clientId=PaymentDetailTable.clientId
AND PaymentDetailTable.paymentId IN (SELECT MAX(paymentId) FROM PaymentDetailTable GROUP BY clientId)