joins!

[Tech]

Hi.
using SQL Server here.

I have 2 tables:

Customers
Transactions

Transactions has a "SenderID" and a "RecepientID" field. Customers contains the CustomersID's

What I want to do is that, I want to pick a specific transactionID and display:

SenderID, SenderCustomerName, RecepientID, RecepientCustomerName

I believe I have to use some join statement?

how would I achieve this? I am not great at SQL
please help!


[edit]
I am wondering if this is correct, as it appears to work but i am sure there is a better much more effecient way?

SELECT Transactions.TransID, Transactions.SenderCustomerID, Transactions.RecepientCustomerID, Transactions.TimeOfTransaction, Transactions.Amount, Transactions.ExchangeRate,
e.FirstName, e.LastName, e.PhoneNumber,
f.FirstName 'RecepientFirstName', f.LastName 'RecepientLastName', f.PhoneNumber 'RecepientPhoneNumber' FROM Transactions

JOIN Customers_Sender e ON
e.[ID] = Transactions.SenderCustomerID

INNER JOIN Customers_Sender f ON f.[ID] = Transactions.RecepientCustomerID
WHERE Transactions.TransID = someTransID

[/edit]

 

[SQL_Hell]

Hi

Syntax looks ok, but why I have you joined the same table twice?

 

[Tech]

no idea! lol - because it works?
how would I go about doing this correctly?

 

[SQL_Hell]

Hi again...

I think this is what you want

SELECT Transactions.TransID, Transactions.SenderCustomerID, Transactions.RecepientCustomerID, Transactions.TimeOfTransaction, Transactions.Amount, Transactions.ExchangeRate,
e.FirstName, e.LastName, e.PhoneNumber,
f.FirstName 'RecepientFirstName', f.LastName 'RecepientLastName', f.PhoneNumber 'RecepientPhoneNumber' FROM Transactions

INNER JOIN Customers_Sender f ON f.[ID] = Transactions.RecepientCustomerID
AND
f.[ID] = Transactions.SenderCustomerID

WHERE Transactions.TransID = someTransID


But why in your database design do RecepientCustomerID and SenderCustomerID have the same value? Or do they not?

 

[Tech]

they are not the same

 

[SQL_Hell]

Ok use an OR instead of AND then