CODEWITHSUNDEEP
mysql
vip32
vip32

Reputation: 77

Complex MySQL select with 2 foreign keys

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

Upvotes: 2

Views: 52

Answers (3)

Marcx
Marcx

Reputation: 6836

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!

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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

Upvotes: 2

Marc B
Marc B

Reputation: 360702

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

Upvotes: 2

Related Questions