Getting all the rows from that one table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Getting all the rows from that one table

Author

Topic

danjapro
Starting Member

44 Posts

Posted - 2006-06-05 : 11:18:45

Here is my current SQL Statment:

However What I am trying to do is I have one table called Orbitimport that stroes all the reocrds in one row then parses it wou among the other tables. I need to be able to return al the records within that one row on Orbitimport table that hase the mose recent ID on it or in other words I need to return the latest ID value. and aall the records that is associated with that Id.

If you need further Explation I can give you.

SELECT
AssetType.Description,
AssetAttribute.AssetDescription,
Asset.Barcode,
Asset.SKU,
ESNTracking.ReportTime,
ESNTracking.CurrLocStreet,
ESNTracking.CurrLocCity,
ESNTracking.CurrLocState,
ESNTracking.CurrLocZip,
ESNTracking.CurrLocCounty,
ESN.EsnNumber,
ESNTracking.DistanceMiles,
InventoryOrigin.WarehouseDescription
FROM
AssetType
INNER JOIN Asset ON (AssetType.AssetTypeId = Asset.AssetTypeId)
INNER JOIN InventoryOrigin ON (Asset.WarehouseId = InventoryOrigin.WarehouseId)
INNER JOIN AssetAttribute ON (Asset.AssetAttributeId = AssetAttribute.AssetAttributeId)
INNER JOIN EsnAsset ON (Asset.AssetId = EsnAsset.AssetId)
INNER JOIN ESN ON (EsnAsset.EsnId = ESN.EsnId)
LEFT OUTER JOIN ESNTracking ON (EsnAsset.EsnId = ESNTracking.EsnId)
LEFT OUTER JOIN AssetVehicle ON (EsnAsset.AssetId = AssetVehicle.AssetId)
LEFT OUTER JOIN AssetCustomAttribute ON (EsnAsset.AssetId = AssetCustomAttribute.AssetId)
LEFT OUTER JOIN AssetCustomAttributeDef ON (AssetCustomAttribute.AssetTypeId = AssetCustomAttributeDef.AssetTypeId)
LEFT OUTER JOIN OrbitImport ON (EsnAsset.EsnId = OrbitImport.OrbitImportId)
WHERE
(CONVERT(varchar(11), EsnAsset.EffectiveDAte, 101) = CONVERT(varchar(11), GetDate(), 101))

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-05 : 11:26:08

danjapro - Post a small example of what you mean (sample data, output you would like for that sample data). It will be much easier to understand what you're asking, and you'll get the answer you need much quicker...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

danjapro
Starting Member

44 Posts

Posted - 2006-06-05 : 14:29:51

Ok,

Basically I have table named OrbitImport, A file is store in a one column before all the data is then sent to the associated tables.

What I need in this query is a way to get the last or most recent import file by orbitimportId to display all the data that is asscoiated with that particalu ID that wa slast imported.

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-05 : 22:30:57

I am not sure what you mean but from your query, i can only see the following
LEFT OUTER JOIN OrbitImport ON (EsnAsset.EsnId = OrbitImport.OrbitImportId)

and you need the latest (i assume, the maximum), thus, remove that and
add in the where clause similar to the following:

SELECT
AssetType.Description,
AssetAttribute.AssetDescription,
Asset.Barcode,
Asset.SKU,
ESNTracking.ReportTime,
ESNTracking.CurrLocStreet,
ESNTracking.CurrLocCity,
ESNTracking.CurrLocState,
ESNTracking.CurrLocZip,
ESNTracking.CurrLocCounty,
ESN.EsnNumber,
ESNTracking.DistanceMiles,
InventoryOrigin.WarehouseDescription
FROM
AssetType
INNER JOIN Asset ON (AssetType.AssetTypeId = Asset.AssetTypeId)
INNER JOIN InventoryOrigin ON (Asset.WarehouseId = InventoryOrigin.WarehouseId)
INNER JOIN AssetAttribute ON (Asset.AssetAttributeId = AssetAttribute.AssetAttributeId)
INNER JOIN EsnAsset ON (Asset.AssetId = EsnAsset.AssetId)
INNER JOIN ESN ON (EsnAsset.EsnId = ESN.EsnId)
LEFT OUTER JOIN ESNTracking ON (EsnAsset.EsnId = ESNTracking.EsnId)
LEFT OUTER JOIN AssetVehicle ON (EsnAsset.AssetId = AssetVehicle.AssetId)
LEFT OUTER JOIN AssetCustomAttribute ON (EsnAsset.AssetId = AssetCustomAttribute.AssetId)
LEFT OUTER JOIN AssetCustomAttributeDef ON (AssetCustomAttribute.AssetTypeId = AssetCustomAttributeDef.AssetTypeId)
WHERE
(CONVERT(varchar(11), EsnAsset.EffectiveDAte, 101) = CONVERT(varchar(11), GetDate(), 101))
AND
EsnAsset.EsnId = (SELECT MAX(OrbitImport.OrbitImportId) FROM OrbitImport )

May the Almighty God bless us all!

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-05 : 22:31:57

You can modify the subquery to return "latest ID value".

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources