→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MrPatrick
Engaged Sweeper
I have the following report.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.IPAddress,
tblVideoController.Caption,
tblNetworkAdapter.Name,
tblIDEController.Caption As Caption1
From tblAssets
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblVideoController
On tblAssets.AssetID = tblVideoController.AssetID
Inner Join tblNetworkAdapter On tblAssets.AssetID = tblNetworkAdapter.AssetID
Inner Join tblIDEController On tblAssets.AssetID = tblIDEController.AssetID


As most of the machines have 2 network adaptors, 2 video adaptors and 3 or more IDE controllers I'm getting a line for each combination of those, so
net adaptor 1 with VGA adaptor 1 and IED adaptor 1
net adaptor 2 with VGA adaptor 1 and IDE adaptor 1
net adaptor 1 with VGA adaptor 2 and IDE adaptor 1

I understand that there is no way to completely flatten this to a single line but I'd like to understand if there is a way to more intelligently join these bits of data to reduce the total number of data output?
1 REPLY 1
Nick_VDB
Champion Sweeper III
As you mentioned getting multiple rows is expected when there is more than one line of data for a field in a table. By simply linking the tables differently you will most likely not get any changes as you are still calling the same tables.

Howerver, There are ways to combine multiple rows into a single row. These do require having advanced knowledge of SQL. You can find information on how to do this online such as here and here.