2

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?

3 Answers 3

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;
5
  • Great job on a simple solution!
    – Element808
    Jan 7, 2017 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 7, 2017 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 7, 2017 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 7, 2017 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 9, 2017 at 15:30
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.

1
  • 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 7, 2017 at 22:30
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

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 7, 2017 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 7, 2017 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 7, 2017 at 23:06
  • Yup @santasatan just gave you the correct answer.
    – Element808
    Jan 7, 2017 at 23:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.