+ Post New Thread
Results 1 to 3 of 3
How do you do....it? Thread, SQL Syntax? in Technical; Hi all, I'm writing a web based Asset Management System for our school at the moment and having difficulty with ...
  1. #1

    Join Date
    May 2008
    Location
    Gosport, Hampshire
    Posts
    36
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    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
    Attached Images Attached Images

  2. #2

    Join Date
    Apr 2006
    Posts
    388
    Thank Post
    23
    Thanked 95 Times in 61 Posts
    Rep Power
    44
    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

  3. #3


    tom_newton's Avatar
    Join Date
    Sep 2006
    Location
    Leeds
    Posts
    4,462
    Thank Post
    866
    Thanked 845 Times in 667 Posts
    Rep Power
    195
    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"

SHARE:
+ Post New Thread

Similar Threads

  1. Proper syntax to delete folder from Multiple Profiles
    By soze in forum How do you do....it?
    Replies: 2
    Last Post: 3rd June 2008, 02:54 PM
  2. SQL
    By mac_shinobi in forum How do you do....it?
    Replies: 5
    Last Post: 6th February 2008, 02:42 PM
  3. MS SQL 2005 - Dump SQL
    By tom_newton in forum Windows
    Replies: 7
    Last Post: 23rd January 2008, 05:10 PM
  4. SQL Anywhere
    By Jobos in forum MIS Systems
    Replies: 5
    Last Post: 12th November 2007, 01:05 PM
  5. Grep syntax for multiple srtings?
    By ChrisH in forum *nix
    Replies: 17
    Last Post: 14th October 2006, 08:20 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •