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)
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
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.
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"