Results 1 to 6 of 6
  1. #1
    Origami is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    3

    How to use multiple Inner Joins and one Outer Join ?

    Hello,

    I'm having problems converting an SQL Server query to Access.

    Let me explain what I try to do.
    I've a DB that contains persons and Relations between these persons.
    I want to find all the couple of peoples that are NOT linked directly, but have a minimum of 3 common relations.
    For example, we could have :
    - Person A linked to persons D, E, F, G, H
    - Person B linked to persons C, E, F, G, L, M



    There's no direct relation between A and B, but both have 3 common relations (E, F, G).

    I may have more than one relation between 2 peoples. These relations should be counted as One.
    For example, if R has 1 relation to D and E, and has 2 relations with F.
    And S has 2 relations with D and 1 with F.
    In my calculations, R and S only have 2 common neighbors (D and F). I'm not counting the multiple relations. So, they don't match the threshold of 3 common neighbors.


    The Person Table has a Unique_ID field.
    The LinkEnd table (which is the relation table) also has a Unique_ID (but I don't use it in my Query), and has the fields Entity_ID1 and Entity_ID2 which serve as foreign keys.


    So, on SQL Server, this is my query (which gives me the expected result) :

    Code:
    Select COUNT(*) as CommonNeighbors, SQ.E1_Unique_Id, SQ.E2_Unique_Id
     FROM (
    Select DISTINCT  E1.Unique_ID As E1_Unique_ID, E2.Unique_ID As E2_Unique_ID, 
     E1.Last_Name AS E1_NOM_, E1.First_Name AS E1_Prénom_, 
     E2.Last_Name AS E2_NOM_, E2.First_Name AS E2_Prénom_,  
     LE1.Entity_ID2 As LE1_Entity_ID2 
     FROM Persons_ E1 
      JOIN LinkEnd LE1 On E1.Unique_Id = LE1.Entity_ID1 
      JOIN LinkEnd LE2 On LE1.Entity_ID2 = LE2.Entity_ID2
      JOIN Persons_ E2 On E2.Unique_Id = LE2.Entity_ID1
     LEFT JOIN LinkEnd LE3 On E1.Unique_Id = LE3.Entity_ID1 And E2.Unique_Id = LE3.Entity_ID2
     WHERE E1.Unique_Id > E2.Unique_Id
     And LE3.Link_ID Is NULL) SQ 
     GROUP BY SQ.E1_Unique_Id, SQ.E2_Unique_Id
     HAVING COUNT (*) >= 3
     ORDER BY COUNT (*) DESC
    I'm trying to do the same in Access. But I'm stuck...

    I've Googled how to create multiple joins in Access, and I found that I need to use parenthesis.
    But so far, I couldn't find the correct syntax for my query...

    Thanks for your help...

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    Also need INNER, RIGHT, or LEFT with each JOIN keyword.

    I use the query builder Design View to help get syntax correct but I am not sure yours can be built in Design View.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Origami is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    3

    My sample Access Db with some data to test

    CommonNeighbors.zip

    Hi, thanks for your reply.

    I've attached here the Zipped DB (simplifed, and with fake data).

    What I did is I then created an SQL Server DB from this Access DB, and ran the following SQL script :

    Code:
    Select COUNT(*) as CommonNeighbors, SQ.E1_Unique_Id, SQ.E2_Unique_Id,
     SQ.E1_NAME, SQ.E2_NAME
     FROM (
    Select DISTINCT  E1.Unique_ID As E1_Unique_ID, E2.Unique_ID As E2_Unique_ID, 
     E1.LastName AS E1_NAME, E2.LastName AS E2_NAME,
     LE1.Entity_ID2 As LE1_Entity_ID2 
     FROM Person E1 
      JOIN LinkTable LE1 On E1.Unique_Id = LE1.Entity_ID1 
      JOIN LinkTable LE2 On LE1.Entity_ID2 = LE2.Entity_ID2
      JOIN Person E2 On E2.Unique_Id = LE2.Entity_ID1
     LEFT JOIN LinkTable LE3 On E1.Unique_Id = LE3.Entity_ID1 And E2.Unique_Id = LE3.Entity_ID2
     WHERE E1.Unique_Id > E2.Unique_Id
     And LE3.Direction Is NULL) SQ 
     GROUP BY SQ.E1_Unique_Id, SQ.E2_Unique_Id, SQ.E1_NOM_, SQ.E2_NOM_
     HAVING COUNT (*) >= 3
     ORDER BY COUNT (*) DESC
    The result I have in SQL Server is :
    - CommonNeighbours : 3 (this is the number of common neighbors)
    - E1_UniqueID : 6
    - E2_UniqueID : 1
    - E1_NAME : F
    - E2_NAME : A

    In the sample DB, Persons A and F don't have a direct link, but they have the following 3 common neighbors : C, D, E.

    Let me know if i(s not clear.

    Thanks a lot

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Access SQL requires the use of brackets in the FROM clause when there are more than 2 tables. Also JOIN is INNER JOIN.

    And you can't LEFT JOIN from 2 tables to 1 without additional sub queries.

    This works but does not produce a result because there are no records where direction is null.

    Code:
    SELECT Count(R.E1_Unique_ID) AS CommonNeighbours, R.E1_Unique_ID, R.E2_Unique_ID, R.E1_NAME, R.E2_NAME
    
    FROM (SELECT DISTINCT E1.Unique_ID AS E1_Unique_ID, E2.Unique_ID AS E2_Unique_ID, E1.LastName AS E1_NAME, E2.LastName AS E2_NAME, LE1.Entity_ID2 AS LE1_Entity_ID2
    FROM ((Person AS E1 INNER JOIN LinkTable AS LE1 ON E1.Unique_ID = LE1.Entity_ID1) INNER JOIN LinkTable AS LE2 ON LE1.Entity_ID2 = LE2.Entity_ID1) INNER JOIN Person AS E2 ON LE2.Entity_ID2 = E2.Unique_ID
    WHERE (((E1.Unique_ID)>[E2].[Unique_Id])))  AS R 
    
    LEFT JOIN LinkTable AS LE3 ON (R.LE1_Entity_ID2 = LE3.Entity_ID2) AND (R.E1_Unique_ID = LE3.Entity_ID1)
    WHERE (((R.E1_Unique_ID)>[LE1_Entity_ID2]) AND ((LE3.Direction) Is Null))
    GROUP BY R.E1_Unique_ID, R.E2_Unique_ID, R.E1_NAME, R.E2_NAME
    HAVING (((Count(R.E1_Unique_ID))>=3))
    ORDER BY Count(R.E1_Unique_ID) DESC

    If the direction criteria is ignored, one result is returned from your example data. Sadly the data in your db does not match the data in your posted example so I can't verify if this actually the required result

  5. #5
    Origami is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2022
    Posts
    3
    Hi,

    Thanks a lot for your help which allowed me to get to the correct SQL script.
    There was an error in your SQL, which was not giving the expected results (probably my fault for not being super clear with my explanations, and the fact that the example in my first post was different to the example in the provided Access DB...)

    But your code allowed me to understand how Access was expecting the combination of Inner and Outer joins...

    The final SQL script, which gives me the expected results is :

    Code:
    SELECT COUNT(*) AS CommonNeighbors, R.E1_Unique_ID, R.E2_Unique_ID
    FROM (
         SELECT DISTINCT E1.Unique_ID AS E1_Unique_ID, E2.Unique_ID AS E2_Unique_ID, E1.LastNAME AS E1_NAME, E2.LastNAME AS E2_NAME, LE1.Entity_ID2 AS LE1_Entity_ID2
         FROM ((Person AS E1 
                  INNER JOIN LinkTable AS LE1 ON E1.Unique_ID = LE1.Entity_ID1) 
                  INNER JOIN LinkTable AS LE2 ON LE1.Entity_ID2 = LE2.Entity_ID1) 
                  INNER JOIN Person AS E2 ON LE2.Entity_ID2 = E2.Unique_ID 
           WHERE (((E1.Unique_ID)>[E2].[Unique_Id])))  AS R 
    LEFT JOIN LinkTable AS LE3 ON (R.E1_Unique_ID = LE3.Entity_ID1) AND (R.E2_Unique_ID = LE3.Entity_ID2)
    WHERE (LE3.Entity_ID2 IS NULL)
    GROUP BY R.E1_Unique_ID, R.E2_Unique_ID
    HAVING COUNT(*) >= 3

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Happy to help - there wasn't an example of the sql in your posted db - I just copied the sql in your first post.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-24-2020, 02:30 AM
  2. Need help with outer-joins
    By MadCrow in forum Queries
    Replies: 4
    Last Post: 06-19-2014, 07:36 AM
  3. Replies: 15
    Last Post: 10-22-2012, 06:06 PM
  4. ambiguous outer joins
    By libraccess in forum Queries
    Replies: 5
    Last Post: 03-31-2012, 05:41 PM
  5. Multiple outer joins - error message
    By Lipi in forum Queries
    Replies: 1
    Last Post: 09-16-2010, 02:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums