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:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

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
Second Degree Black Belt

Please log in to comment

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
Please log in to comment
Answer this question or Comment on this question for clarity