how to join the same table twice?

[s481797]

i don't know if this is a very good topic title, but finding a title is almost more complicated than the problem itself... ;-)

i've got 2 tables: 'device_relation' and 'device'. device relation connects one device to another device.

so in device_relation i've got 3 fields: device_relation_id, output_device_id and input_device_id.

for example: 1, 1, 2. the out_device_id and input_device_id are foreign keys to the 'device' table.

device has got 2 fields: device_id and name.

for example: 1, DVD and 2, TV. so, according to my device_relation table, a DVD is connected to a TV. so far, so good.

however, i can't find a proper query to show both DVD and TV instead of the output_device_id and input_device_id when i select all records from 'device_relation'. i have been busy with joins, but i can't join the device table twice.

does anybody have an idea? thanks in advance!

[wickning1]

You just have to give it two different aliases.

[code]SELECT i.name, o.name FROM device i
INNER JOIN device_relation r ON r.input_device_id=i.device_id
INNER JOIN device o ON r.output_device_id=o.device_id[/code]

[s481797]

thanks!