Using parent SQL column in subquery

Good morning. I’m trying to pull the username of the user from the column in to_id. Is there. It’d be simple if I was just filtering on to_id, but I also need records from another column from_id. I’ve attempted doing a UNION to get around this issue, but it only pulls records from user.id 3 of course.

Does anyone happen to know a way around this? I’m somewhat new to writing complex SQL queries. Haven’t been able to figure much out from similar questions.

SELECT
users.username, -- Placeholder until username from to_id can be pulled
payment.id,
to_id,
amount,
state,
type,
timedate
FROM
    payment
LEFT JOIN users ON users.id = payment.to_id AND users.id = payment.from_id 
WHERE to_id = 3 OR from_id = 3

The result of that would be along the lines of:

+----------+----+-------+--------+----------+------+---------------------+
| username | id | to_id | amount | state    | type | timedate            |
+----------+----+-------+--------+----------+------+---------------------+
| NULL     |  1 |     1 |  12.56 | COMPLETE | u2u  | 2021-11-12 06:09:21 |
| NULL     |  2 |     1 |  43.00 | COMPLETE | u2u  | 2021-11-12 06:17:10 |
| NULL     |  3 |     3 |   2.25 | COMPLETE | u2u  | 2021-11-12 06:22:53 |
+----------+----+-------+--------+----------+------+---------------------+

Username is null due to the two Joins being AND. If it’s OR, the username will show up, but the rows will be there twice. Once with the to_id username, once with the from_id username.

Answer

So you have one users table for all payers and payees accounts and one transaction table with two ID columns (payer and payee)? You need to join the users table to the transaction table twice, once to get the payer info, once to get the payee info.

select
    payment.from_id,
    from_user.username,
    payment.to_id,
    to_user.username,
    payment.id,
    amount,
    state,
    type,
    timedate
from payment
left join users as from_user 
    on from_user.id = payment.from_id 
left join users as to_user 
    on to_user.id = payment.to_id 
where payment.to_id = 3 OR payment.from_id = 3