/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


SQL Link between Asset tables and Machines tables

12/04/2014 1028 views

Does anybody know the relationship link between the asset table and the machine table without getting a cartesian product.

 

I can't find the link other then a union and I end up with a cartesian product.

 

Select
  ORG1.ASSET.NAME As "Asset Table Name",
  ORG1.ASSET_DATA_5.FIELD_10008 As "Asset Table Status",
  ORG1.MACHINE.CS_MODEL As "Inventory Table Model",
  ORG1.MACHINE.CS_MANUFACTURER
From
  ORG1.ASSET_DATA_5 Left Outer Join
  ORG1.ASSET On ORG1.ASSET.ASSET_DATA_ID = ORG1.ASSET_DATA_5.ID Left Outer Join
  ORG1.MACHINE On ORG1.MACHINE.NAME = ORG1.ASSET.NAME
Where
  ORG1.ASSET_DATA_5.FIELD_10008 Like 'In Inventory'
Order By
  ORG1.ASSET_DATA_5.FIELD_10008

 

Answer Summary:
0 Comments   [ + ] Show comments

Comments



Community Chosen Answer

1

here is the scripted I used to pull the field "In Inventory". use a left outer join.

Select
  ORG1.ASSET.NAME As "Asset Table Name",
  ORG1.ASSET_DATA_5.FIELD_10008 As "Asset Table Status",
  ORG1.MACHINE.CS_MODEL As "Machine Table Model",
  ORG1.MACHINE.NAME As "Machine Table Name",
  ORG1.ASSET.MAPPED_ID,
  ORG1.USER.CUSTOM_2 As "Department",
  ORG1.ASSET_DATA_5.FIELD_46 As "Asset Location",
  ORG1.ASSET_DATA_5.FIELD_86 As "Asset Comments",
  ORG1.ASSET_DATA_5.FIELD_39 As "Asset Purchase Date",
  ORG1.ASSET_DATA_5.FIELD_89 As "Dell Service Tag",
  ORG1.ASSET_DATA_5.FIELD_10052 As "Inventoried?"
From
  ORG1.ASSET_DATA_5 Left Outer Join
  ORG1.ASSET On ORG1.ASSET.ASSET_DATA_ID = ORG1.ASSET_DATA_5.ID Left Outer Join
  ORG1.MACHINE On ORG1.MACHINE.NAME = ORG1.ASSET.NAME left outer Join
  ORG1.USER On ORG1.MACHINE.USER_NAME = ORG1.USER.USER_NAME
Where
  ORG1.ASSET_DATA_5.FIELD_10008 Like 'In Inventory'
Order By
  ORG1.ASSET_DATA_5.FIELD_10008

Answered 12/08/2014 by: mikesharp1
2nd Degree Black Belt

All Answers

0
you can do a join via the BIOS Serial Number

join ASSET_DATA_5 as AD5 on AD5.YOURBIOSFIELD = MACHINE.BIOS_SERIAL_NUMBER
Answered 12/04/2014 by: h2opolo25
Red Belt

  • I think I figure out what was happening. If there is no data for the inventory machine then you will get a 0 for mapped_id field for asset. thank you

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ