SQL Link between Asset tables and Machines tables
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
Answers (2)
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
Comments:
-
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 - mikesharp1 9 years ago