How would you join two columns in the same table?

[imgrooot]

Basically i want to match 2 columns from table1 to 1 column in table2.  

 

Here's my code. How do I do the joins properly?

$get_earnings = $db->prepare("SELECT users.*, member_earnings.* FROM member_earnings
LEFT JOIN users ON member_earnings.sent_by = users.user_id
LEFT JOIN users ON member_earnings.guest_id = users.user_id
WHERE record_id = :record_id AND status = :status ORDER BY earning_id DESC LIMIT 20");
$get_earnings->bindParam(':record_id', $url_id);
$get_earnings->bindValue(':status', 1);
$get_earnings->execute();

Edited January 7, 2018 by imgrooot

[kicken]

If you join the same table multiple times, you need to give the joins an alias so you can distinguish between them.

 

SELECT sentBy.*, guest.*, member_earnings.* 
FROM member_earnings
LEFT JOIN users as sentBy ON member_earnings.sent_by = sentBy.user_id
LEFT JOIN users as guest ON member_earnings.guest_id = guest.user_id
WHERE record_id = :record_id AND status = :status 
ORDER BY earning_id DESC LIMIT 20

[imgrooot]

If you join the same table multiple times, you need to give the joins an alias so you can distinguish between them.

 

SELECT sentBy.*, guest.*, member_earnings.* 
FROM member_earnings
LEFT JOIN users as sentBy ON member_earnings.sent_by = sentBy.user_id
LEFT JOIN users as guest ON member_earnings.guest_id = guest.user_id
WHERE record_id = :record_id AND status = :status 
ORDER BY earning_id DESC LIMIT 20

 

Perfect. That works like a charm. Thank you.