Results 1 to 9 of 9

Thread: Joins

  1. #1
    Join Date
    Nov 2007
    Posts
    16

    Unanswered: Joins

    Here's my problem:

    I have 5 tables (Seller, SaleProperty, ViewingAppointment, PotentialBuyer, StaffMember)
    I need to produce a list of viewing appointments that will include:

    Date & time of appointment
    Seller's name
    Potential buyer's name
    Property's address

    However, when I created a 'normal' join I ended up with every possible combination of results, meaning that I ended up with 48,000 records.
    How do I ensure that I only end up with 1 'set' of correct records rather than having displayed all of the possible combinations from all the data in the tables?

    Hopefully that makes sense

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your "normal" join wasn't normal enough

    what columns did you join on? please show your query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    make sure your joins are on unique values (using he primary and foreign key realtionships)
    make sure your joins uniquely identify the records in the rwo tables.
    have you established a realtionship between the various tables, preferably using an RI key.. if so the query designer usually selects the correct join characteristics, the only problem is when you need to tweak the join to say inlcude all appointments and those records that are relevant from table B

    the nature of the join clause is that it is flexible, but you have to tell it precisely what you want.

    make sure the join is going from the foreign key tot he primary key, don't try and join to tables based on foreign keys

    the best way of debugging this sort of problem is to look at the sql itself, or post it here. along with the table essential details (names and the primary & foreign Keys)

  4. #4
    Join Date
    Nov 2007
    Posts
    16
    Here are the details of my tables:

    Viewing Appointment Table
    AppointmentCode (PK)
    AppointmentTime
    AppointmentDate
    StaffCode (FK - (PK of Staff Member Table))
    HouseCode (FK - (PK of Sale Property Table))
    PotentialBuyerCode (FK - (PK of Potential Buyer Table))

    Sale Property Table
    HouseCode (PK)
    PropertyAddress
    Town
    NoOfBedrooms
    NoOfBathrooms
    NoOReceptionRooms
    Garage
    PropertyType
    FreeholdOrLeasehold
    SaleStatus
    DatePlacedOnMarket
    SellerCode (FK - (PK of Seller Table))

    Potential Buyer Table
    PotentialBuyerCode (PK)
    Address
    TelephoneNumber
    EmailAddress
    PreferredArea
    MinNoOfBedrooms
    MinNoOfBathrooms
    MinNoOfReceptionRooms
    Garage
    MaxPrice

    Seller Table
    SellerCode (PK)
    SellerName

    Staff Member Table
    StaffCode (PK)
    StaffName

    Here's my query:

    Well i've just tryed doing it again and it seems to work - this time I only have 20 records which seems fine!Anyway, here is the query:

    SELECT PotentialBuyer.Name, Seller.SellerName, SaleProperty.PropertyAddress, StaffMember.StaffName, ViewingAppointment.AppointmentDate, ViewingAppointment.AppointmentTime

    FROM PotentialBuyer, Seller, StaffMember, ViewingAppointment, SaleProperty

    WHERE SaleProperty.HouseCode = ViewingAppointment.HouseCode AND PotentialBuyer.PotentialBuyerCode = ViewingAppointment.PotentialBuyerCode AND StaffMember.StaffCode = ViewingAppointment.StaffCode AND SaleProperty.SellerCode = Seller.SellerCode;

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so is this the query before or after it seems to work
    if it now seems to work what did you changes

    given that you may have multiple appointments for the same property spannign different days Im not surprised if you get multiple results.. I would have expected some form of date filter on the appointment

    there is no order by clause, so its quite possible that the qeury parser isa interpreting your requirements differently based on the previous queries. SO I think you need to be far more explicit in how you define your queries. Although the where style is still supported, generally its better to use the 'join' syntax.. have a look at the help file for join syntax


    the cheats ways of doing that is to design the query in the query designer and make sure your tables use the RI links. For me personally there little point in using a relational db like Access/JET without defining the RI links, OK I appreciate that many people don't see Access/JET as a true relational db... and they are correct, but it does have relational "tendencies"

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    make sure the join is going from the foreign key tot he primary key, don't try and join to tables based on foreign keys
    could you please explain what you were attempting to say here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    ... many people don't see Access/JET as a true relational db... and they are correct
    they are not correct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2007
    Posts
    16
    so is this the query before or after it seems to work
    The query I posted is the one that gives the correct output. To make life simpler, I allocated one property only one appointment for one house, and after checking through the tables, the output seems to be as desired.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think(!) what I'm trying to get at is to make sure that the join goes "through" the table with the PK in it

    a "ferisntance"
    say you have a table with product details, PK ProdID, and two other tables which both use ProdID in them joining the two child tables on ProdID is valid, and could be what was intended, but could lead to more records being returned than intended or required......

    mind you as I understand it:- in JET SQL the precendence of the join is important, ie which table is being joined to which other table using what columns.

    that applies both to the specific join term and the sequence in which the joins across multiple tables are presented. If there is no clear sequence then JET can sometimes take decisions itself which impact on the rows returned.

Posting Permissions

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