I have a basic messaging system set up and I m using the same query to show sent and received messages. It works very well, but I need to get the fbook field for exp and dest. I thought of creating two queries and use one if the user sent the message he is viewing and use the other query if the message has been recieved, but maybe there's a better way.

+-----+------+------+----------+----------------+---------------------+------+
| id  | exp  | dest | message  | msg_title      | timestamp           | view |
+-----+------+------+----------+----------------+---------------------+------+
| 114 |  243 |  245 | From 243 | Message to 245 | 2012-04-04 09:26:52 |    0 | 
+-----+------+------+----------+----------------+---------------------+------+

Query

SELECT a.*, b.fbook FROM messages a 
                            JOIN users b ON a.exp=b.id
                            WHERE a.id = 114;

Users table

+------------------------+--------------+------+-----+---------------------+----------------+
| Field                  | Type         | Null | Key | Default             | Extra              |
+------------------------+--------------+------+-----+---------------------+----------------+
| id                     | int(11)      | NO   | PRI | NULL                |  auto_increment | 
| username               | varchar(50)  | NO   |     | NULL                |                  |  
| fbook                  | bigint(64)   | YES  |     | NULL                |                    | 
+------------------------+--------------+------+-----+---------------------+----------------+

• mysql

You want to do something like this:

SELECT a.*, b.fbook AS `exp-fbook`, c.fbook AS `dest-fbook`
FROM messages a 
JOIN users b ON a.exp=b.id
JOIN users c ON a.dest=c.id
WHERE a.id = 243

Use two JOINs with the same table. Is better for performance if you use LEFT JOINs than INNER JOINS.

SELECT
    m.*, uexp.fbook, udest.fbook
FROM messages m 
    LEFT JOIN users uexp ON m.exp = uexp.id
    LEFT JOIN users udest ON m.dest = udest.id
WHERE m.id = 243;

You can try (I would think the a.id you want is 114 not 243):

SELECT a.*, ue.fbook AS fbokkExp, ud.fbook AS fbookDest
FROM messages a 
JOIN users ue ON a.exp = ue.id
JOIN users ud ON a.dest = ud.id
WHERE a.id = 114;