CODEWITHSUNDEEP
mysql
Ciprian
Ciprian

Reputation: 3226

Mysql query for messaging system. A small bug I can't figure out

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                |                    | 
+------------------------+--------------+------+-----+---------------------+----------------+

Upvotes: 1

Views: 58

Answers (3)

JScoobyCed
JScoobyCed

Reputation: 10413

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;

Upvotes: 1

jordeu
jordeu

Reputation: 6821

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;

Upvotes: 1

Daan
Daan

Reputation: 3348

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

Upvotes: 2

Related Questions