• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

Join a Table where the two columns need to be linked to single column in joined table

Hi,

I have the situation where I need to join a table where two columns within this table need to be linked to
the same column in the other (join) table. I use the following script below but it seems this does not
retrive the result sets i wanted. Can someone please help?

SELECT  
  t1.[PaymentID],
  t1.[PaymentFrom],  t2.[Client Name],
  t1.[PaymentTo],  t2.[Client Name]
FROM [Payments] t1 INNER JOIN [ClientDetails] t2
ON t1.[PaymentFrom] = t2.[ClienId] AND t1.[PaymentTo] = t2.[ClientId]


Table [Payments]
  [OrderID]
  [PaymentID]
  [PaymentFrom]
  [PaymentTo]

Table [ClientDetails]
  [ClientID]
  [Client Name]
  [Contact]
0
Senior IT System Engineer
Asked:
Senior IT System Engineer
  • 2
1 Solution
 
Paul_Harris_FusionCommented:
I am guessing that the payment from client might be different to the payment to client.  
In which case, you need to include the client table twice

SELECT  
  P.[PaymentID],
  P.[PaymentFrom],  F.[Client Name] as FROM_NAME
  P.[PaymentTo],  T.[Client Name] as TO_NAME
FROM Payments P
INNER JOIN ClientDetails F ON P.PaymentFrom = F.ClientId
INNER JOIN ClientDetails T ON P.PaymentTo = T.ClientId
0
 
Paul_Harris_FusionCommented:
Hello - have you abandoned this question?    If so could you close it off in some way and let me know if my solution was successful.......
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now