Problem with sql code

Well, am guessing that you just want to to list out Caption, DeviceRole and IP Address.  If so, the query is a bit off.  Try this:

Select

Nodes.Caption AS ServerName,

Nodes.DeviceRole AS DeviceRole,

Nodes.IP_Address AS Ips

FROM dbo.Nodes

Not sure why you were including a subselect there, for information from the same table. 

Select

Nodez.Caption as Servername,

Nodez.DeviceRole as DeviceRole,

IPAddr.IPAddress as 'IP Addresses'

FROM dbo.Nodes AS Nodez

join dbo.NodeIPAddresses as IPAddr on Nodez.NodeID = IPAddr.NodeID

I Fixed it