Monday, March 28, 2011

MySQL - Need help to figure out multiple joins

I am using the following query to get the transactions from a table made to and from a user. I then want to retrieve the username for the sender_id and for the recipient_id. However I can only seem to get it for the recipient_id or the sender_id. Anyone have any ideas how I can get both.

SELECT us.name, ta.amount, ta.recipient_id, ta.sender_id, ta.timestamp_insert
     FROM  `transactions` AS ta
     JOIN users AS us
     ON ta.recipient_id=us.u_id
     WHERE ta.sender_id =111111 OR ta.recipient_id = 111111
     LIMIT 0 , 10

Transactions Table Columns:

transaction_id
tw_id
tw
sender_id
recipient_id
amount
timestamp_insert
timestamp_start timestamp_complete transaction_status

User Table Columns:

u_id, name

From stackoverflow
  • You need to join twice, thus:

    SELECT ta.amount, ta.recipient_id, ta.sender_id, ta.timestamp_insert, sender.name as Sender, recipient.name as Recipient
            FROM  `transactions` AS ta
            JOIN users AS recipient
            ON ta.recipient_id=recipient.u_id
            JOIN users AS sender
            ON ta.sender_id=sender.u_id
            WHERE ta.sender_id =111111 OR ta.recipient_id = 111111
            LIMIT 0 , 10
    

0 comments:

Post a Comment