JOIN with one column to get appropriate client names in two separate columns-CodePudding

I'm trying to fetch Originating & Terminating customers in two different columns as per their Account IDs but my join is giving me the same result in the output columns.

All customers are in vbClient.clCustomerID column.

Getting below Output:

Required Output:

Below is the tables output

Here is my query

SELECT vbClient.clCustomerID 'Originating Customer', vbClient.clCustomerID 'Terminating Customer'
FROM Successfuliptsp.vbSuccessfulCDR_634, iTelBillingiptsp.vbClient
WHERE vbClient.clAccountID = vbSuccessfulCDR_634.orgClientAccountID
AND vbClient.clAccountID = vbSuccessfulCDR_634.terClientAccountID

CodePudding user response：

SELECT t2.clCustomerID AS `Originating Customer`, 
       t3.clCustomerID AS `Terminating Customer` 
FROM Successfuliptsp.vbSuccessfulCDR_634 AS t1 
JOIN iTelBillingiptsp.vbClient AS t2 ON t1.orgClientAccountID = t2.clAccountID 
JOIN iTelBillingiptsp.vbClient AS t3 ON t1.terClientAccountID = t3.clAccountID