Complex MySQL select with 2 foreign keys

Question

I am attempting to do a select between 3 tables. The only problem is that one of the tables is used to resolve foreign keys on 2 different tables. A short description of the tables can be found below. I have removed some columns so that only pertinent ones are shown.

mail_addr
+------------+
| id         |
+------------+
| email      |
+------------+

msg_rcpt
+------------+
|MsgID       |
+------------+
|rid         |
+------------+
|content     |
+------------+

msgs
+------------+
|MsgID       |
+------------+
|sid         |
+------------+
|msgTime     |
+------------+
|size        |
+------------+

I have attempted...

SELECT msg_rcpt.MsgID, msg_rcpt.content, mail_addr.email as rcpt_addr, msgs.msgTime
FROM msg_rcpt
JOIN msgs ON msgs.MsgID = msg_rcpt.MsgID
JOIN mail_addr ON msg_rcpt.rid = mail_addr.id
JOIN mail_addr ON msgs.sid = mail_addr.id

When I do these joins it comes back with Not unique table/alias: 'mail_addr'. I know this is wrong but I am unsure how or even what I should be looking for in order to resolve this query.

Any feedback would be appreciated.

Viper

possible duplicate of [Why does this SQL code give error 1066 (Not unique table/alias: 'user')?](http://stackoverflow.com/questions/1435177/why-does-this-sql-code-give-error-1066-not-unique-table-alias-user) — TimoStaudinger, May 28 '15 at 15:06

score 2 · Accepted Answer · answered May 28 '15 at 15:02

2

You can join a table multiple times, but you HAVE to alias the 2nd and subsequent joins:

JOIN mail_addr ON msg_rcpt.rid = mail_addr.id
JOIN mail_addr AS someaslias ON msgs.sid = somealias.id
               ^^^^^^^^^^^^^               ^^^^^^^^^

answered May 28 '15 at 15:02

Marc B

356,200
43
426
500

score 2 · Answer 2 · answered May 28 '15 at 15:02

If you wanna join two times on the same table, you'll have to use aliases (well at least one, but two aliases will make things clearer).

SELECT msg_rcpt.MsgID, msg_rcpt.content, m1.email as rcpt_addr, msgs.msgTime
FROM msg_rcpt
JOIN msgs ON msgs.MsgID = msg_rcpt.MsgID
JOIN mail_addr m1 ON msg_rcpt.rid = m1.id
JOIN mail_addr m2 ON msgs.sid = m2.id

score 0 · Answer 3 · answered May 28 '15 at 15:02

I did not understand if this is the result you want to achieve but this should work (based on your example query)

SELECT msg_rcpt.MsgID, msg_rcpt.content, m1.email as rcpt_addr, msgs.msgTime 
FROM msg_rcpt JOIN msgs ON msgs.MsgID = msg_rcpt.MsgID 
JOIN mail_addr m1 ON msg_rcpt.rid = m1.id 
JOIN mail_addr m2 ON msgs.sid = m2.id

If this is not what you're expecting, please edit your question adding an example of the final result!

Complex MySQL select with 2 foreign keys

3 Answers3