SELECT JOIN problem!

[Anon]

This is kind of complicated. OK, I have a table tb1 with fields: 'fromid', 'toid' and table tb2 with fields: 'id', 'name'. In which 'fromid' and 'toid' are 'id' in tb2. Now I need to make a SELECT statement join two tables to return 'fromid', 'toid', 'tb2.name' AS 'fromname' (which 'fromid'='id'), 'tb2.name' AS 'toname' (which 'toid'='id'). This is an example:

tb1:

fromid | toid
1 | 2
1 | 3
2 | 1

tb2:

id | name
1 | 'abcd'
2 | 'edfg'
3 | 'hijk'
The SELECT * will return
fromid | toid | fromname | toname
1 | 2 | 'abcd' | 'edfg'
1 | 3 | 'abcd' | 'hijk'
2 | 1 | 'edfg' | 'abcd'

Please help. Thanks.

[Anon]

You will be joining twice on tb2, since you will have two tbl2 rows per row of tb1. Use aliases for tbl2.

SELECT fromid, toid, from_table.name AS
fromname, to_table.name AS toname
FROM tbl1 INNER JOIN
tb2 to_table ON (tb1.toid = to_table.id)
INNER JOIN tb2 from_table ON (tbl1.fromid = from_table.id)

[Anon]

Awesome!! It works great. Thanks.