Join 1 column to multiple columns in another table

Question

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

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

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?

score 2 · Accepted Answer · edited Jan 08 '17 at 02:26

2

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;

edited Jan 08 '17 at 02:26

mustaccio

25,896
22
57
72

answered Jan 07 '17 at 22:55

user114494

36
1

Great job on a simple solution! – Element808 Jan 07 '17 at 23:13
for the view I needed to create the below is what ended up working for my needs so far:
<code> SELECT d1.hostname AS [networkdevice hostname], p.port, p.timestamp, p.description, d2.hostname AS [connectedDevice hostname] FROM dbo.Devices AS d1 INNER JOIN dbo.portmap AS p ON d1.ID = p.networkDeviceID INNER JOIN dbo.Devices AS d2 ON p.connectedDeviceID = d2.ID </code>
– heywould Jan 07 '17 at 23:21
Do you have any advice on what subject I should look into for better understanding of your solution. or what would\did you google? – heywould Jan 07 '17 at 23:38
@heywould if you are asking about the SQL I wrote, they are just plain SQL statements, nothing fancy. you can find SQL tutorial anywhere. I guess the only thing is that I was using from ... where to join 3 tables, you are using inner join. In this case, they are just equivalent. – user114494 Jan 07 '17 at 23:49
@heywould: Although likely not affecting the performance (and certainly not affecting the results), your script would make more sense if written with a different order of joins: SELECT ... FROM dbo.portmap AS p INNER JOIN dbo.Devices AS d1 ON ... INNER JOIN dbo.Devices AS d2 ON .... Good job on figuring out the (currently) standard way of writing joins based on this answer, by the way. People can have their preferences all they like, but the newer syntax (which isn't really new, being 20+ years old) is objectively clearer, on average, to a newcomer, so that's what we should promote. Cheers. – Andriy M Jan 09 '17 at 15:30

Element808 · Answer 2 · 2017-01-07T22:39:48.027

0

So just switch your normal 'AND' in your join, to an 'OR', and it will get all of the devices.

SELECT        dbo.portmap.Id, dbo.portmap.ConnectedDeviceId, dbo.portmap.networkDeviceId, dbo.Devices.hostname
FROM            dbo.Devices INNER JOIN
                         dbo.portmap ON dbo.Devices.Id = dbo.portmap.networkDeviceId OR dbo.Devices.Id = dbo.portmap.ConnectedDeviceId

EDIT: Oh, if you just are trying to get the hostname field on a normal join, then you just need to specify identifiers for each of your tables along with the fields/columns you want to get. For instance, "select dbo.Devices.hostname, dbo.portmap.Id" will get you the hostname of the Devices table and the Id of the portmap

edited Jan 07 '17 at 22:39

answered Jan 07 '17 at 21:36

Element808

113
4

please see the edit above, I added another picture. how do I get the hostname from device table to show in the networkdeviceID column and in the conntecteddeviceID column? – heywould Jan 07 '17 at 22:37
Just saw the edit just now...will re-revise my answer (don't worry about the previous edit in my post) – Element808 Jan 07 '17 at 22:40
So I may not be able to provide this to you immediately. I've done this before, but it's been rare. It's a combination of getting the correct expressions to show the correct data and it may be a mixture of nested joins that provide the view what you want it to. If I had more time, I could probably figure it out. I'll keep at it, but I'm sure a more experience db guru will probably get it before I do. – Element808 Jan 07 '17 at 23:06
Yup @santasatan just gave you the correct answer. – Element808 Jan 07 '17 at 23:10

score 0 · Answer 3 · answered Jan 07 '17 at 22:15

0

Try this:

SELECT d.hostname
FROM Devices d 
INNER JOIN portmap p ON d.ID = p.ID
WHERE p.networkdeviceID = 2 and p.ConnecteddeviceID = 1

I hope that I understood you well.

answered Jan 07 '17 at 22:15

Filip Holub

179
3

no that gave me just a single hostname. ID NID Port Date des CiD 1 1 g0/1 2017-01-06 asdf 2 2 2 g0/2 2017-01-06 fdsa 1 – heywould Jan 07 '17 at 22:30

Join 1 column to multiple columns in another table

3 Answers3