CODEWITHSUNDEEP
sqldatabasepostgresqlselectjoin
Jarmund
Jarmund

Reputation: 3205

JOIN across two tables

I need to extract some data from a postgresql database, taking a few elements from each of two tobles. The tables contain data relating to physical network devices, where one table is exclusively for mac addresses of these devices. Each device is identified by location (vehicle) and function (dev_name).

table1 (assets):

vehicle
dev_name
dev_serial
dev_model

table2: (macs)

vehicle
dev_name
mac
interface

What i tried:

SELECT assets.vehicle, assets.dev_name, dev_model, dev_serial, mac
    FROM assets, macs
    AND interface = 'E0'
    ORDER BY vehicle, dev_name
;

But it seems to not be matching vehicle and dev_name as i thought it would. Instead it seems to print every combination of mac and dev_serial, which is not the intended output, as i want one line for each.

How would one make sure that it matches the mac address to the device based on assets.dev_name = macs.dev_name and assets.vehicle = macs.vehicle?

Note: Some devices in assets may not have a recorded mac address in mac, in which case i want them displayed anyway with an empty mac

Upvotes: 0

Views: 154

Answers (3)

Bodhi
Bodhi

Reputation: 354

A simple join keyword is the solution of your problems

SELECT assets.vehicle, assets.dev_name, dev_model, dev_serial, mac
FROM assets A join macs M
ON A.dev_name = M.dev_name

Otherwise going on with your approach, just modify the query as follows:

SELECT assets.vehicle, assets.dev_name, dev_model, dev_serial, mac
FROM assets, macs
WHERE interface = 'E0' AND assets.dev_name = macs.dev_name
ORDER BY vehicle, dev_name;

remember that for any join the condition is very important, other wise it just becomes another cross join, i.e. combination of each row with every other row.

Upvotes: 0

roman
roman

Reputation: 117380

Read about SQL join.

select
   a.vehicle, a.dev_name, a.dev_model, a.dev_serial, m.mac
from assets as a
    inner join macs as m on m.dev_name = a.dev_name and m.vehicle = a.dev_name 
where m.interface = 'E0'
order by a.vehicle, a.dev_name

In your case it's inner join, but if you want to get all assets and show mac for those with interface E0, you could use left outer join:

select
   a.vehicle, a.dev_name, a.dev_model, a.dev_serial, m.mac
from assets as a
    left outer join macs as m on
        m.dev_name = a.dev_name and m.vehicle = a.dev_name and m.interface = 'E0'
order by a.vehicle, a.dev_name

Upvotes: 0

juergen d
juergen d

Reputation: 204766

When using a join you can specify which columns have to match

SELECT a.vehicle, a.dev_name, dev_model, dev_serial, mac
FROM assets a
LEFT JOIN macs m ON m.vehicle = a.vehicle 
                 AND m.dev_name = a.dev_name
WHERE interface = 'E0'
ORDER BY a.vehicle, a.dev_name

Upvotes: 3

Related Questions