I am still using NetDot and Love it.

I am working on a SQL query to run against the database that will hit a couple tables to provide a report for our cost accounting group.

Here is my original SQL, it works, but is missing data –

select
d.sysname, d.sysdescription, d.last_updated, d.date_installed, a.serial_number, a.product_id, a.inventory_number
from device d, asset a
where d.asset_id = a.id
AND a.inventory_number like 'a%';

The problem is that in a device, there are modules that’s have assets that has an inventory number and that data is not matching up

Is there any way you can help me create a SQL script that will get the missing data?

Here is my most recent SQL but I am still striking out, getting duplicate data and missing data.

select
d.sysname, d.syslocation, d.last_updated, d.date_installed, d.asset_id,a.serial_number, a.inventory_number
from device d, asset a, devicemodule m, devicemodule e
where m.asset_id = d.asset_id AND m.device = d.id and d.asset_id = a.id and a.id = e.asset_id
AND a.inventory_number like 'A%'

My end goal is to get the following fields -
d.sysname, d.syslocation, d.last_updated, d.date_installed, d.asset_id,a.serial_number, a.inventory_number

Any guidance you can give me would be very much appreciated.

Thanks again,

-Mike
_
Michael T. Voity | Network Engineer | Telecommunications & Network Services| Enterprise Technology Services | The University of Vermont |