Problem Introduction:
I’m working on a faker e-banking system as a side project for practicing my SQL skills (which are super bad), and I’m now stuck at a point where I want to pull data from two tables but cannot do that with joins
So, basically I have a users table which looks like this:
And I have a transactions table which saves the sender id, receiver id, amount, and date. And it looks like this:
What I want to achieve:
Now, I want to create a query that extracts the data in the transactions table in a way such that if I specified an ID of n (somewhere in the query), I’d get all of the transactions that user with ID of n made in a way that it displays the first name of both sender and receiver of all of theses transactions.
I’ve been doing a lot of joins/subquery stuff to extract that information but I really can’t seem to find a solution.
I’m using PostgreSQL btw.
Notes:
- Sender & Receiver are not the same person
- I want to display the first name of both the sender & receiver
Advertisement
Answer
You can achieve this by joining to the users
table twice (you’ll have to give it a separate name for each join). The following snippet leaves out some detail but illustrates the idea –
SELECT ... FROM transactions t INNER JOIN users s ON t.sender = s.id INNER JOIN users r ON t.receiver = r.id