We help IT Professionals succeed at work.

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]
Comment
Watch Question

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.