Jump to content


Photo

how to join the same table twice?


  • Please log in to reply
2 replies to this topic

#1 s481797

s481797
  • Members
  • Pip
  • Newbie
  • 4 posts
  • LocationNijmegen, The Netherlands

Posted 28 March 2006 - 11:51 AM

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!

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 28 March 2006 - 01:14 PM

You just have to give it two different aliases.

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


#3 s481797

s481797
  • Members
  • Pip
  • Newbie
  • 4 posts
  • LocationNijmegen, The Netherlands

Posted 30 March 2006 - 08:29 AM

thanks!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users