Would like help with SQL Query

[mvoity]

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 |

[nicolatron]

Not sure this is what you want, but to me when doing complex queries, using join helps me not getting crazy.
And playing with inner/outer left/right join can help get more or less stuff.

Something in the line of this...

SELECT
d.sysname, d.syslocation, d.last_updated, d.date_installed, d.asset_id,a.serial_number, a.inventory_number
FROM device d
JOIN asset a on d.asset_id = a.id
where a.inventory_number like 'A%';

[mvoity]

Nicolás,

Thanks for your quick response, I really do appreciate it.

The Join you provided didn't provide any more results than the original query.

I think the Join I need has something to do with the devicemodule table, asset table and then device table.

my sql skills are sub par, any idea on how to do that join?

Thanks again for looking and your help.

-Mike

[nicolatron]

Hello Mike,

Another shot...

SELECT device.sysname, device.syslocation, device.last_updated, device.date_installed, device.asset_id, asset.serial_number, product.name, inventory_number
FROM device
LEFT JOIN asset on device.asset_id = asset.id
LEFT JOIN devicemodule on devicemodule.asset_id = asset.id
JOIN product on asset.product_id = product.id
group by device.id order by device.id, date_installed;

[mvoity]

Nicolás, Thank again for your reply, I changed it to this: SELECT device.sysname, device.syslocation, device.last_updated, device.date_installed, device.asset_id, asset.serial_number, product.name, inventory_number FROM device LEFT JOIN asset on device.asset_id = asset.id LEFT JOIN devicemodule on devicemodule.asset_id = asset.id JOIN product on asset.product_id = product.id and inventory_number like 'A%' group by device.id order by device.id, date_installed; It returned 174 rows I did this against the asset table: SELECT a.serial_number, a.inventory_number FROM asset a where a.inventory_number like 'A%'; I get 325 rows, Which is what I am looking for, My goal is to get the all the asset s/n’s and inventory to match all the devices. In 1 SQL query that can dump to the cost accounting people. Does this help? Thank you again for your help!

…

-Mike _ Michael T. Voity | Network Engineer | Telecommunications & Network Services| Enterprise Technology Services | The University of Vermont | (802) 656-8112 | ***@***.******@***.***> From: Nicolás Victorero ***@***.*** Sent: Tuesday, February 8, 2022 4:32 PM To: cvicente/Netdot ***@***.***> Cc: Michael T. Voity ***@***.***>; Author ***@***.***> Subject: Re: [cvicente/Netdot] Would like help with SQL Query (Issue #167) Hello Mike, Another shot... SELECT device.sysname, device.syslocation, device.last_updated, device.date_installed, device.asset_id, asset.serial_number, product.name, inventory_number FROM device LEFT JOIN asset on device.asset_id = asset.id LEFT JOIN devicemodule on devicemodule.asset_id = asset.id JOIN product on asset.product_id = product.id group by device.id order by device.id, date_installed; — Reply to this email directly, view it on GitHub<#167 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AH2Z4I3DFQ56CPH72N7T4G3U2GDVNANCNFSM5N2XDOLA>. Triage notifications on the go with GitHub Mobile for iOS<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you authored the thread.Message ID: ***@***.******@***.***>>