This might be obvious to folks who spend more time in MySQL than I do, but this came up recently when working on a messaging system, and might be useful to somebody.

Say you have a table with multiple foreign keys to the same table… In my case, I had a messages table that had an FK of the user that sent the message, and an FK of the user that the message was sent to. You can run multiple JOINS with aliases, and SELECT them by the alias.

I wanted a single query to get back information about the message, as well as details about both users. Assume the tables look roughly like this (simplified and abbreviated for clarity):

CREATE TABLE messages (
  id INT,
  message TEXT,
  date DATETIME,
  sender INT,
  receiver INT
)
CREATE TABLE users (
  id INT,
  name VARCHAR(255)
)

Then the query could look like this:

SELECT 
  messages.message,
  messages.date,
  sender.name as senderName,
  receiver.name as receiverName
FROM
  messages
LEFT OUTER JOIN 
  users sender ON messages.sender = senders.id
LEFT OUTER JOIN 
  users receiver ON messages.receiver = receiver.id
WHERE
  messages.id = :messageId