Link 2 tables, but JOIN 2 times.

[Mgccl]

I have 2 tables

Table:tag_tag_link
tag_id1|tag_id2

Table:tag
tag_id|tag_name

Now, I want to join these 2 tables, but with 2 join statement because each tag_id in tag_tag_link refer to the tag_id in tag...

what can I do, like this?

SELECT *
FROM tag_tag_link
LEFT JOIN tag ON tag1_id = tag_id
LEFT JOIN tag ON tag2_id = tag_id

surely that doe not seems right... any ideas?

[Piranha]

Try something like this:

SELECT ttl.id, ttl.name, tag.id   // You should select what to return even through it is not nessecary in this case.
FROM tag_tag_link as ttl
LEFT JOIN tag as tag ON tag1_id = tag_id OR tag2_id = tag_id // Simply use the OR command to get both.

This is a way that works. But you should retrieve the tag_id from ttl since it would require two colomns that you would have to handle otherwise.

[Lars_Berg]

SELECT tag1.name,tag2.name
FROM tag as tag1 join tag_tag_link
 ON tag1.tag_id = tag1_id 
  JOIN tag as tag2 ON tag2_id = tag2.tag_id