Results 1 to 2 of 2

Thread: Another Query problem

  1. #1
    Join Date
    May 2008
    Posts
    6

    Another Query problem

    I need to retrieve data from three tables and every syntax error I solve seems to cause several more issues! (I am a basic user of queries)

    Please could anyone read what I have done below and suggest a way of getting this to work? 'Searches' contains Job_Refs, and joins to 'Product' to identify the name of the product being requested. 'Searches' needs to join to 'Plan' via CaseFile which holds the PlanID number. ('Searches' therefore must also join CaseFile on CaseFileID


    Select a.ExternalWebServiceID as Job_Ref,
    Product.OurCode as Report_Type,
    plan.PointEastings as Site_Easting,
    plan.PointNorthing as Site_Northing,
    CaseFile.PlacedDate as Report_Date,
    '\\blah\blah\' + b.ExternalWebServiceID + 'sxw' as FilePath

    from

    Searches a
    join Product on Searches.ProductID = Product.ProductID,
    Searches b
    join CaseFile on Searches.CaseFileId = CaseFile.CaseFileID,
    Casefile join Plan on CaseFile.PlanId = Plan.PlandID

  2. #2
    Join Date
    Jun 2008
    Posts
    2
    remove the comma's from the "from" statements, and each table only needs to be defined once...

    Select a.ExternalWebServiceID as Job_Ref,
    Product.OurCode as Report_Type,
    plan.PointEastings as Site_Easting,
    plan.PointNorthing as Site_Northing,
    CaseFile.PlacedDate as Report_Date,
    '\\blah\blah\' + a.ExternalWebServiceID + 'sxw' as FilePath

    from

    Searches a
    join Product on Searches.ProductID = Product.ProductID
    join CaseFile on a.CaseFileId = CaseFile.CaseFileID
    join Plan on CaseFile.PlanId = Plan.PlandID


    Currently, a record in the searches table that does not have a corresponding record in product (with same productid) will not be returned, as inner joins are being used. If you want to return all records from searches regardless of whether product information exists, then you need to use a left join.


    Quote Originally Posted by neil-j
    I need to retrieve data from three tables and every syntax error I solve seems to cause several more issues! (I am a basic user of queries)

    Please could anyone read what I have done below and suggest a way of getting this to work? 'Searches' contains Job_Refs, and joins to 'Product' to identify the name of the product being requested. 'Searches' needs to join to 'Plan' via CaseFile which holds the PlanID number. ('Searches' therefore must also join CaseFile on CaseFileID


    Select a.ExternalWebServiceID as Job_Ref,
    Product.OurCode as Report_Type,
    plan.PointEastings as Site_Easting,
    plan.PointNorthing as Site_Northing,
    CaseFile.PlacedDate as Report_Date,
    '\\blah\blah\' + b.ExternalWebServiceID + 'sxw' as FilePath

    from

    Searches a
    join Product on Searches.ProductID = Product.ProductID,
    Searches b
    join CaseFile on Searches.CaseFileId = CaseFile.CaseFileID,
    Casefile join Plan on CaseFile.PlanId = Plan.PlandID

Posting Permissions

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