Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Statement (A2000)

    The following SQL returns vehicles, clients booked in.

    It is designed from four tables.

    In my db I know there are five clients booked in, but the SQL will only return two.

    I know its because various fields in the four tables contain null values, how can I bypass these so five entries are returned.

    SELECT tblDetails.EstimateNo, tblDetails.Supp, tblDetails.Registration, tblDetails.JobType, tblClientCode.Surname, tblEnquiry.BookInDate, tblEnquiry.BookOutDate, tblDetails.Registration, tblVehicleDetails.Manufacturer, tblVehicleDetails.Model
    FROM tblVehicleDetails INNER JOIN (tblEnquiry INNER JOIN (tblDetails INNER JOIN tblClientCode ON tblDetails.ClientCode = tblClientCode.ClientCode) ON (tblEnquiry.Supp = tblDetails.Supp) AND (tblEnquiry.EstimateNo = tblDetails.EstimateNo)) ON (tblVehicleDetails.Supp = tblDetails.Supp) AND (tblVehicleDetails.EstimateNo = tblDetails.EstimateNo) AND (tblVehicleDetails.EstimateNo = tblDetails.EstimateNo) AND (tblVehicleDetails.Registration = tblDetails.Registration);


    Dave

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Statement (A2000)

    Inner joins return only those records for which the join field is the same and not null on both sides. You can change them to outer joins so that null values on one side of the join are allowed.

    In query design view, double click a line representing a join. You'll get 3 options. The first option is the inner join; the other two are the left and right outer join. You'll have to decide which one you want. I'd start with the join between tblClientCode and tblDetails and select the option where all records in tblClientCode are displayed. Then both links from tblDetails to tblEnquiry - select the option where all records in tblDetails are displayed. Finally the link from tblEnquiry to tblVehicleDetails.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2000)

    I'm getting ambigous join errors ??

    What does this meen ?

    Thanks

    Dave
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Statement (A2000)

    Dave,

    It means that there is an inconsistency in the "direction" of the joins.

    tblA ---> tblB ---> tblC ---> tblD is OK

    tblA <--- tblB ---> tblC ---> tblD is OK too (probably)

    tblA ---> tblB <--- tblC ---> tblD is ambiguous

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2000)

    Hans
    This does make sense, a bit like driving up a one way street isn't it.

    I have removed all the joins, saved and re-opened with a view to re-join with the correct directions.
    I have tried to re- join one at a time but still get the ambigous error, is their something in the index of the tables ?

    Dave

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement (A2000)

    Thanks Hans, I've got it now : -

    SELECT tblDetails.EstimateNo, tblDetails.Supp, tblDetails.Registration, tblDetails.JobType, tblClientCode.Surname, tblEnquiry.BookInDate, tblEnquiry.BookOutDate, tblDetails.Registration, tblVehicleDetails.Manufacturer, tblVehicleDetails.Model
    FROM tblVehicleDetails INNER JOIN (tblEnquiry INNER JOIN (tblDetails INNER JOIN tblClientCode ON tblDetails.ClientCode = tblClientCode.ClientCode) ON (tblEnquiry.EstimateNo = tblDetails.EstimateNo) AND (tblEnquiry.Supp = tblDetails.Supp)) ON (tblVehicleDetails.Registration = tblDetails.Registration) AND (tblVehicleDetails.EstimateNo = tblDetails.EstimateNo) AND (tblVehicleDetails.EstimateNo = tblDetails.EstimateNo) AND (tblVehicleDetails.Supp = tblDetails.Supp);

    Dave

Posting Permissions

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