Results 1 to 10 of 10

Thread: Join Properties

  1. #1
    Join Date
    Apr 2010
    Posts
    49

    Unanswered: Join Properties

    I still can't get this to work out

    I have three tables (one is a junction) and no matter the combination I use, I can only get results from one table to come back.

    If I left join to the junction and right join to the other table I only get left results and the opposite if i switch directions. Access only gives three options when you click on join properties, so I'm not sure what to do...


    Thoughts?

  2. #2
    Join Date
    May 2010
    Posts
    601
    It would really help to know more details about your tables.

    What work be great is if you could post a sample database. Include the three tables with sample data that show the issue. Also include the queries that you have tried.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Three tables:
    Code:
    Song
    -----
    You are the pootle of my eye
    Jump flump jump
    Oh where oh where is posie?
    Code:
    Singer
    -----
    Perkin
    Mother
    Grandfather
    Code:
    SongSinger
    -----
    You are the pootle of my eye    Perkin
    You are the pootle of my eye    Mother
    Jump flump jump                 Perkin
    What results do you want?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2010
    Posts
    49
    Here's the tables I'm trying to query. I want all the songs and performers to come back in the results, but all I can get is one or the other. I read somewhere that it might not be possible and I may have to do two queries followed by a union query...?
    Attached Thumbnails Attached Thumbnails relatinship.JPG  

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Should meterid be performerid?

    Based on that sample data you don't even need outer joins to get the results I think you want, just inner joins. Can you post your query?

    PS - a small tip - never, ever use Right Outer Joins - they serve no purpose apart from make your SQL very difficult to read.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2010
    Posts
    49
    whoops, yep it should be performer.

    I tried the following:

    song left to junction and right to performer = songs only
    songs right to junction and left to performer = performers only

    anything else get's me no results and access only gives you 3 choices...any thoughts?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Never use right join - they suck.
    Use two inner joins. If this does not work please copy and paste the SQL from the query.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2010
    Posts
    49
    I believe that's the default but I don't get anything back in my results

    SELECT Performer.FName, Performer.LName, Song.Song, Song_Performer.SongID, Song_Performer.PerformerID
    FROM Song INNER JOIN (Performer INNER JOIN Song_Performer ON Performer.PerformerID = Song_Performer.PerformerID) ON Song.SongID = Song_Performer.SongID;

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If that is your data and that is your query then it will return everything.I'm guessing then the problem is your data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2010
    Posts
    49
    cool, I deleted all my data and started over. So now it works, I must have had an error somewhere Thanks!

Posting Permissions

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