Joining problem in sql database

Question

0.00/5 (No votes)

See more:

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

Posted 30-May-13 22:46pm

GDdixit

Add a Solution

Comments

abdussalam143 31-May-13 9:06am

as i consider you want to display all records of paymentDetail with client and address detail am i right

or

please add some discribtion some sort of record you want to get like blow

clientId | addressid | paymentid
1 | 12 | 101
2 | 13 | 103
1 | 12 | 102

GDdixit 1-Jun-13 7:10am

i want to show in this manner .

clientId | addressid | paymentid
1 | 12 | 102
2 | 13 | 103

i.e. with max paymentid of that client ....

how to do this ?

2 solutions

Solution 1

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

Posted 30-May-13 22:58pm

Vipin kumar.P

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rajesh VLND · Accepted Answer · 2013-05-31T20:52:00

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)