Multiple left (I think) joins to obtain unrelated records from junction table

All we need is an easy explanation of the problem, so here it is.

I have a database in which many-to-many relationships between records (e.g., customers, suppliers, products) work through a junction table that contains the ID (a unique code (i.e., no two records in the database, regardless of their table, have the same ID), not an autonumber) of the two related records.

tbl_Customer
Auto Nr | UniqueID | FirstName | LastName | ...
------------------------------------------------
1       | 10239    | Joe       | Bloggs   | ...
2       | 15028    | Andrew    | Smith    | ...

tbl_Supplier
Auto Nr | UniqueID | CompanyNo | CompanyName | ...
------------------------------------------------
9       | 83762    | 112320    | Walmart     | ...
10      | 36492    | 129920    | Texaco      | ...

tbl_Junction
Auto Nr | UniqueID_A | UniqueID_B |
------------------------------------------------
1       | 10239      | 83762      |

Records can be related to records of any type (e.g., a supplier can be related to another supplier, or to a customer).

I’m looking for a way to find, for a given record (say, a supplier) the records in a given table (say, tbl_Customer) that it is not related to. So, in the above example the query result would be Andrew Smith. The purpose is to populate a list of records that aren’t linked (the user will click to add a link to a record, and the list will be refreshed; the reverse will happen for already linked records).

The problem I have is that there’s no way of knowing whether the tbl_Customer.UniqueID is in tbl_Junction.UniqueID_A or tbl_Junction.UniqueID_B. (For a given type of relationship, e.g., customer-supplier, this could be reasonably easily resolved by just always putting the supplier UniqueID in _A and the customer UniqueID in _B — but that obviously won’t work where the relationship is between e.g., two customers).

I can work out the formulation of the left join for either side easily enough:

SELECT *
FROM tbl_Customer
LEFT JOIN tbl_junction
ON tbl_Customer.UniqueID=tbl_junction.UniqueID_A
WHERE tbl_Junction.UniqueID_A IS Null

But how do I construct the join so that it captures either scenario?

Any help greatly appreciated!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Given your setup, you know that a customer linked to a given supplier can be found either in

SELECT
  UniqueID_A
FROM
  tbl_Junction
WHERE
  UniqueID_B = @GivenSupplierUniqueID

or in

SELECT
  UniqueID_B
FROM
  tbl_Junction
WHERE
  UniqueID_A = @GivenSupplierUniqueID

Therefore, in order to get the customers not linked to the said supplier, you need to select the tbl_Customer rows that are not found in either:

SELECT
  *
FROM
  tbl_Customer
WHERE
  UniqueID NOT IN
  (
    SELECT
      UniqueID_A
    FROM
      tbl_Junction
    WHERE
      UniqueID_B = @GivenSupplierUniqueID
  )
  AND
  UniqueID NOT IN
  (
    SELECT
      UniqueID_B
    FROM
      tbl_Junction
    WHERE
      UniqueID_A = @GivenSupplierUniqueID
  )
;

You can, of course, UNION the two subsets and thus use a single NOT IN, not sure if it will make it faster, but you can probably easily test this for yourself:

SELECT
  *
FROM
  tbl_Customer
WHERE
  UniqueID NOT IN
  (
    SELECT
      UniqueID_A
    FROM
      tbl_Junction
    WHERE
      UniqueID_B = @GivenSupplierUniqueID

    UNION ALL

    SELECT
      UniqueID_B
    FROM
      tbl_Junction
    WHERE
      UniqueID_A = @GivenSupplierUniqueID
  )
;

You can also use the subsets as derived tables in order to implement this anti-join using left joins (or a single left join against a UNION-ed set):

SELECT
  *
FROM
  tbl_Customer AS c
  LEFT JOIN
  (
    SELECT
      UniqueID_A
    FROM
      tbl_Junction
    WHERE
      UniqueID_B = @GivenSupplierUniqueID
  ) AS a_in_b ON c.UniqueID = a_in_b.UniqueID_A
  LEFT JOIN
  (
    SELECT
      UniqueID_B
    FROM
      tbl_Junction
    WHERE
      UniqueID_A = @GivenSupplierUniqueID
  ) AS b_in_a ON c.UniqueID = b_in_a.UniqueID_B
WHERE
  a_in_b.UniqueID_A IS NULL
  AND
  b_in_a.UniqueID_B IS NULL
;

Method 2

find, for a given record (say, a supplier) the records in a given table (say, tbl_Customer) that it is not related to.

Looks like

SELECT *
FROM tbl_Customer
WHERE NOT EXISTS ( SELECT NULL
                   FROM tbl_Junction J
                   JOIN tbl_Supplier S ON J.UniqueID_B = S.UniqueID 
                   WHERE J.UniqueID_A = C.UniqueID )

If the value in tbl_Junction cannot not exist in related table then

SELECT *
FROM tbl_Customer
WHERE NOT EXISTS ( SELECT NULL
                   FROM tbl_Junction J
                   WHERE J.UniqueID_A = C.UniqueID )

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply