Jump to content

Archived

This topic is now archived and is closed to further replies.

s481797

how to join the same table twice?

Recommended Posts

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!

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.