SQL Syntax?

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

Code:

---

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.

K

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"