Join 1 column to multiple columns in another table

join;

I have 2 tables portmap and devices

Portmap has the following columns.
ID (which is the PK),
networkDeviceID (FK to device table),
port,
timestamp,
description,
connectedDeviceID (FK to device table)

Devices has the following columns.
ID (which is the PK),
modelID (FK to the model table),
hostname,
serialnumber,
lanmac,
wmac

enter image description here

I'm having a mental block on how to get the correct corresponding hostname to display when doing a join. for example:

if row 1 in portmap table cotains the following
networkdeviceID = 2 and ConnecteddeviceID = 1

how do I get it to display the hostname for each IDvalue instead of just giving me the INT vaules

edit

how do I get the hostname from deviceID to show in the networkdeviceID column and in the conntecteddeviceID column?

edit2

enter image description here

maybe this might help you understand what I need.

I would like have the hostname mvl displayed in the networkdeviceID field and mvd displayed in the connectedDeviceID field on row 1. row 2 when then display mvd for the networkdeviceID and mvl for the connectedDeviceID field.

I need it that way so if say a end user decided to change the hostname mvl to say xyz1234 every record throughout the DB wouldn't automatically update to xyz123 instead of having to changing it by hand. or if in the portmap table they changed row2 networkdebiceID to 7 it would automaticlly display the device table hostname of mvdxxxxx

Do you get what I'm trying to do now?

Best Answer

Try something like this:

SELECT p.ID, d1.hostname, p.port, p.timestamp, p.description, d2.hostname
FROM Devices d1, Portmap p, Devices d2
WHERE d1.id = p.networkdeviceID and d2.id = p.connecteddeviceID;
Related Question