none
Cannot get a Left Join to work (but Union Select works fine) RRS feed

  • Question

  • I am attempting to use a LEFT JOIN for two queries instead of a Union as I want to reduce the number of records pulled from the second table.  The Union SQL statement is as follows and works fine:

    SELECT qry6IndEncountersInRangetest.*

    FROM qry6IndEncountersInRangetest

    UNION SELECT qry6GroupEncountersInRange.*

    FROM qry6GroupEncountersInRange;

    The statement as modified as a LEFT JOIN follows:

    SELECT qry6IndEncountersInRangetest.*
    FROM qry6IndEncountersInRangetest LEFT JOIN qry6GroupEncountersInRange ON qry6IndEncountersInRangetest.PersonID =
    qry6GroupEncountersInRange.PersonID;

    The two queries as part of the LEFT JOIN follow:

    qry6IndEncountersInRangetest:

    SELECT hld6PersonIndividualEncounterModified.EncounterCat, hld6PersonIndividualEncounterModified.PersonID, hld6PersonIndividualEncounterModified.EncounterDate, hld6PersonIndividualEncounterModified.EncounterID, hld6PersonIndividualEncounterModified.AgencyCode, hld6PersonIndividualEncounterModified.SiteCode, hld6PersonIndividualEncounterModified.ServiceCategory, hld6PersonIndividualEncounterModified.ServiceType, hld6PersonIndividualEncounterModified.EncounterType, hld6PersonIndividualEncounterModified.TimeHours, hld6PersonIndividualEncounterModified.TimeMinutes, hld6PersonIndividualEncounterModified.TravelTimeHours, hld6PersonIndividualEncounterModified.TravelTimeMinutes, hld6PersonIndividualEncounterModified.EncounterStaff, hld6PersonIndividualEncounterModified.EncounterStaff2, hld6PersonIndividualEncounterModified.EncounterStaff3
    FROM hld6PersonIndividualEncounterModified
    WHERE (((hld6PersonIndividualEncounterModified.EncounterDate) Between [Forms]![frmReportSubmenuParametersCG]![StartDate] And [Forms]![frmReportSubmenuParametersCG]![EndDate]));

    qry6GroupEncountersInRange:

    SELECT "Group" AS EncounterCat, tblGroupServiceParticipants.PersonID, qry6GroupServiceModified.GroupDate, qry6GroupServiceModified.GroupID, qry6GroupServiceModified.AgencyCode, qry6GroupServiceModified.SiteCode, qry6GroupServiceModified.GroupServiceCategory, qry6GroupServiceModified.ServiceType, qry6GroupServiceModified.EncounterType, qry6GroupServiceModified.TimeHours, qry6GroupServiceModified.TimeMinutes, 0 AS TravelTimeHours, 0 AS TravelTimeMinutes, qry6GroupServiceModified.GroupStaff, qry6GroupServiceModified.GroupStaff2, qry6GroupServiceModified.GroupStaff3
    FROM qry6GroupServiceModified INNER JOIN tblGroupServiceParticipants ON qry6GroupServiceModified.GroupID = tblGroupServiceParticipants.GroupID
    WHERE (((qry6GroupServiceModified.GroupDate) Between [Forms]![frmReportSubmenuParametersCG]![StartDate] And [Forms]![frmReportSubmenuParametersCG]![EndDate]));

    After I run the query the Individual transactions appear in my report.  However, none of the group service transactions appear, even though I verified several have identical personIds to those in the Individual transactions table.

    The tblGroupServiceParticipants table, referenced in the qry6GroupServiceModified, does actually contain a composite key, namely Group Number and Person ID, but I believe all I need to do is filter on the Person ID as is shown in the LEFT JOIN.  I would appreciate any input on this so I can get this LEFT JOIN working properly and return the valid matched records from the qry6GroupServiceModified portion.

    Thanks again.

    Larry Staley

    Friday, September 23, 2016 11:10 PM

Answers

  • SELECT qry6IndEncountersInRangetest.* FROM ... means that you only return records from qry6IndEncountersInRangetest. You don't mention qry6GroupEncountersInRange in the SELECT part.

    If you want to return a record from qry6GroupEncountersInRange in the same row as the record from qry6IndEncountersInRangetest it matches too, you should use

    SELECT qry6IndEncountersInRangetest.*, qry6GroupEncountersInRange.* FROM ...

    But since the field names are the same, the query name would be included in each and every column header. I doubt that that is what you want.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Wednesday, October 5, 2016 1:35 PM
    • Marked as answer by David_JunFeng Wednesday, October 5, 2016 1:35 PM
    Saturday, September 24, 2016 9:08 AM
  • Hi Hans,

    I got it to work too.  Slight variation on the code, but mine added Inner Join, with the Group as the left table, to the already existing Union as:

    SELECT qry6IndEncountersInRangetest.*
    FROM qry6IndEncountersInRangetest
    UNION SELECT qry6GroupEncountersInRange.*
    FROM
    qry6GroupEncountersInRange  INNER JOIN qry6IndEncountersInRangetest ON
    qry6GroupEncountersInRange.PersonID = qry6IndEncountersInRangetest.PersonID;

    Thanks again for your help.  Now I have a string challenge so will add a new post.

    L. Staley

    • Proposed as answer by David_JunFeng Wednesday, October 5, 2016 1:35 PM
    • Marked as answer by David_JunFeng Wednesday, October 5, 2016 1:35 PM
    Tuesday, September 27, 2016 3:31 AM

All replies

  • SELECT qry6IndEncountersInRangetest.* FROM ... means that you only return records from qry6IndEncountersInRangetest. You don't mention qry6GroupEncountersInRange in the SELECT part.

    If you want to return a record from qry6GroupEncountersInRange in the same row as the record from qry6IndEncountersInRangetest it matches too, you should use

    SELECT qry6IndEncountersInRangetest.*, qry6GroupEncountersInRange.* FROM ...

    But since the field names are the same, the query name would be included in each and every column header. I doubt that that is what you want.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by David_JunFeng Wednesday, October 5, 2016 1:35 PM
    • Marked as answer by David_JunFeng Wednesday, October 5, 2016 1:35 PM
    Saturday, September 24, 2016 9:08 AM
  • Hans,

    I will try that and let you know.  This may be actually what I need.  Consider this is how it works:

    Individual Encounter:

    Select following PersonIDs:

    A18

    B24

    B81

    C19

    In the Group Encounters, each of these Persons could be part of any Group (that is the other key on the Group side, namely GroupID)

    So assume following exist in the Group:

    GroupID:  25 and contains PersonIDs F26, C20, and D14

    34 contains A18, C44, and E15

    22 contains B24, C19, and F16

    In this case WHAT I need returned are only group IDs, 34 and 22 as they contain matched PersonIDs.  The fact there are additional PersonIDs in the group does not concern me.  However, under no circumstances, do I want to pick up GroupID 25.  Hope this simple example explains it.  If what you are indicating also picks up GroupID 25 than will not work.  Maybe additional match needed on that GroupID but that is a foreign key as far as the Individual query side.  Can only match on PersonID.

    Thanks again.

    L. Staley

    Saturday, September 24, 2016 4:02 PM
  • The SQL from my previous reply would return something like this (I left out almost all fields, obviously):


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, September 24, 2016 6:24 PM
  • Hi Hans,

    I got it to work too.  Slight variation on the code, but mine added Inner Join, with the Group as the left table, to the already existing Union as:

    SELECT qry6IndEncountersInRangetest.*
    FROM qry6IndEncountersInRangetest
    UNION SELECT qry6GroupEncountersInRange.*
    FROM
    qry6GroupEncountersInRange  INNER JOIN qry6IndEncountersInRangetest ON
    qry6GroupEncountersInRange.PersonID = qry6IndEncountersInRangetest.PersonID;

    Thanks again for your help.  Now I have a string challenge so will add a new post.

    L. Staley

    • Proposed as answer by David_JunFeng Wednesday, October 5, 2016 1:35 PM
    • Marked as answer by David_JunFeng Wednesday, October 5, 2016 1:35 PM
    Tuesday, September 27, 2016 3:31 AM
  • Hi Larry Staley,

    If you have any issue, please feel free post them on this forum. We will provide help as soon as possible.

    Thanks for your understanding.
    Tuesday, September 27, 2016 9:34 AM