SQL Syntax?

[ScottStevinson]

Hi all,

 

I'm writing a web based Asset Management System for our school at the moment and having difficulty with the SQL Syntax for a report. I'm using SQL Server Reporting Services, and used the Query Builder in Visual Studio to design a report using this code. For some reason it is returning zero rows when executed. Anyone know why? (DB Diagram Attached)

 

Thanks

 

SELECT     TblAssets.AssetID, TblAssets.CategoryID AS Category, TblAssets.SubCatID AS [sub Category], TblAssets.LocationID AS Location, 
                     TblAssets.ManufacturerID AS Manufacturer, TblAssets.ModelID AS Model, TblAssets.SerialNo AS [serial No], TblAssets.AcqDate AS [Acquisition Date], 
                     TblAssets.AcqPrice AS [Purchase Price], TblAssets.Ownership
FROM         TblAssets INNER JOIN
                     TblCategories ON TblAssets.CategoryID = TblCategories.CategoryID INNER JOIN
                     TblLocations ON TblAssets.LocationID = TblLocations.LocationID INNER JOIN
                     TblManufacturers ON TblAssets.ManufacturerID = TblManufacturers.ManufacturerID INNER JOIN
                     TblModels ON TblAssets.ModelID = TblModels.ModelID AND TblManufacturers.ManufacturerID = TblModels.ManufacturerID INNER JOIN
                     TblScrapCompanies ON TblAssets.ScrapCompanyID = TblScrapCompanies.ScrapCompanyID INNER JOIN
                     TblScrapReasons ON TblAssets.ScrapReasonID = TblScrapReasons.ScrapID INNER JOIN
                     TblStatus ON TblAssets.StatusID = TblStatus.StatusID INNER JOIN
                     TblSubCategories ON TblCategories.CategoryID = TblSubCategories.CatID INNER JOIN
                     TblSuppliers ON TblAssets.SupplierID = TblSuppliers.SupplierID
WHERE     (TblAssets.StatusID = 1) OR
                     (TblAssets.StatusID = 2) OR
                     (TblAssets.StatusID = 3)
ORDER BY Location, TblAssets.AssetID

[theriver]

Have you got data in all of the tables? If you're trying to join like this via a table with no data in it, you can get 0 rows returned.

 

Feel free to send over the DB you're prototyping against and I'll have a look.

 

K

[tom_newton]

Build up your query bit by bit, a join at a time, rather than trying the "all at once" approach, you'll soon see which join is multiplying by 0

 

also... for your where claiuse, it can be tidier to ise "WHERE TblAssets.StatusID IN (1,2,3)" or "WHERE TblAssets.StatusID BETWEEN 1 AND 3"