help with Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

help with Query

Author

Topic

danjapro
Starting Member

44 Posts

Posted - 2006-05-30 : 11:44:05

Here is my Query below, in SQL server as a view.
I am trying to return all the Assets with the correct information.

I need a

statment I dont know where to place it . I need all th Assets from Assets table to return all the following info

CODE::::::::::::::::

SELECT Asset.AssetId, AssetType.Description, Asset.Barcode, AssetAttribute.AssetDescription, Asset.SKU, ESN.EsnNumber,
InventoryOrigin.WarehouseDescription, ESNTracking.TraceTime, ESNTracking.PreviousTraceTime, ESNTracking.HasMoved,
ESNTracking.DistanceMiles, ESNTracking.Direction, ESNTracking.Landmark, ESNTracking.FemaLocation,
ESNTracking.ReportTime, ESNTracking.ReplaceByDate, ESNTracking.CurrLocStreet, ESNTracking.CurrLocCity,
ESNTracking.CurrLocState, ESNTracking.CurrLocZip, ESNTracking.CurrLocCounty, ESNTracking.CurrMileFromStratix,
ESNTracking.PrevMileFromStratix
FROM AssetType INNER JOIN
Asset ON AssetType.AssetTypeId = Asset.AssetTypeId INNER JOIN
AssetAttribute ON AssetType.AssetTypeId = AssetAttribute.AssetTypeId AND
Asset.AssetAttributeId = AssetAttribute.AssetAttributeId INNER JOIN
AssetVehicle ON Asset.AssetId = AssetVehicle.AssetId INNER JOIN
AssetCustomAttribute ON Asset.AssetId = AssetCustomAttribute.AssetId INNER JOIN
AssetCustomAttributeDef ON AssetType.AssetTypeId = AssetCustomAttributeDef.AssetTypeId AND
AssetCustomAttribute.AssetCustomAttributeDefId = AssetCustomAttributeDef.AssetCustomAttributeDefId AND
AssetCustomAttribute.AssetTypeId = AssetCustomAttributeDef.AssetTypeId INNER JOIN
InventoryOrigin ON Asset.WarehouseId = InventoryOrigin.WarehouseId INNER JOIN
EsnAsset ON Asset.AssetId = EsnAsset.AssetId INNER JOIN
ESNTracking INNER JOIN
ESN ON ESNTracking.EsnId = ESN.EsnId ON EsnAsset.EsnId = ESN.EsnId

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-30 : 11:58:18

Is this query working ? Correct ?
U want to check this query or improve it or do what ?
Do u want to invoke this (I mean to get the results of this)?

U want to test this or u want to include this in a program ?
What Front end language r u using ?

Srinika

danjapro
Starting Member

44 Posts

Posted - 2006-05-30 : 12:17:23

It is built in as a view in my SQL 2000 Server. The problem is it is not returning any results whatso ever.

The table Assets, ESN, Asste Attributes, AssteType and ESN Asstes all have data in them that are all related but it does not return any theing manily becase ther eis not a a where statment.

I would like to get the ESN in ESN table that Assocites with the Asset in Assets Table.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-30 : 12:29:00

How did u try to get the return data ?
Query Analyzer ? Some other front end ?
If So how ?

Did u try to run the query portion of the View in Query Analyzer and check the results ?
If u did try, then the reason may be
individual tables may have data but when joined there may not be having "Common Data"

Try
Select * from ESN
Select * from Assets

Select * from ESN e
inner join Assets a on e.<Field joining the 2 tables> = a.<Field joining the 2 tables>

Srinika

X002548
Not Just a Number

15586 Posts

Posted - 2006-05-30 : 12:35:33

Why don't you read the hint link in my sig and post what it says there

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-30 : 15:58:16

I have re-formatted the query plus switched to using aliases for clarity. I have removed some of the columns in the SELECT statement as I got bored. You'll need to fix that part up.

BTW, it is very apparent that you used Enterprise Manager to build this query. Do not keep clicking on things until you get it right as that'll just build a very ugly query that probably won't perform well. You need to get familiar with inner joins.

Try this:


SELECT 
	a.AssetId, 
	ast.[Description], 
	a.Barcode, 
	aa.AssetDescription, 
	a.SKU, 
	e.EsnNumber, 
	ino.WarehouseDescription, 
	et.TraceTime, 
	...
FROM AssetType ast
INNER JOIN Asset a
ON ast.AssetTypeId = a.AssetTypeId 
INNER JOIN AssetAttribute aa
ON 	ast.AssetTypeId = aa.AssetTypeId AND 
	a.AssetAttributeId = ast.AssetAttributeId 
INNER JOIN AssetVehicle av
ON a.AssetId = av.AssetId 
INNER JOIN AssetCustomAttribute aca
ON a.AssetId = aca.AssetId 
INNER JOIN AssetCustomAttributeDef acad
ON 	ast.AssetTypeId = acad.AssetTypeId AND 
	aca.AssetCustomAttributeDefId = acad.AssetCustomAttributeDefId AND 
	aca.AssetTypeId = acad.AssetTypeId 
INNER JOIN InventoryOrigin ino
ON a.WarehouseId = ino.WarehouseId 
INNER JOIN EsnAsset ea
ON a.AssetId = ea.AssetId 
INNER JOIN ESN e
ON ea.EsnId = e.EsnId
INNER JOIN ESNTracking et
ON et.EsnId = e.EsnId

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources