1

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!

4
  • 1
    Do I read correctly that you have a single junction table to link any entity with any entity? If so, that doesn't seem to me like a good idea, to put it mildly. I've worked with a project using something similar but at least the junction table also had two additional columns for the entity type in each reference column. Basically, we had two entity type columns and two entity ID columns, but even that I consider a bad idea, having worked with that setup 10+ years. Yours looks to me really messed up, no offence.
    – Andriy M
    Commented Jun 3, 2021 at 12:50
  • @AndriyM - Yes, you read correctly. I agree it's not super-desirable, but I think it was originally done this way to avoid the need for a junction table for each conceivable kind of relationship (of which there are many--the above is a simplification). but in any event, because records can link to records of the same type, dividing the junction table up in the way you say (or even adding a type column) doesn't solve the problem. (Though i guess you could solve it by just adding two records in the junction table for those relationships -- essentially just a mirror image of each other.)
    – mactreb
    Commented Jun 3, 2021 at 13:20
  • @mactreb: If you needed to link two instances of the same entity type, then both entity type columns would contain the same type description (or type ID or whatever): ('customer', 'supplier', 10239, 83762), ('supplier', 'supplier', 36492, 83762), ('customer', 'customer', 15028, 10239) – I can see no problem in that particular area. Having to specify the types would make querying more cumbersome, but at least your queries wouldn't have to scan the entire junction table when checking for only a specific combination of entity types.
    – Andriy M
    Commented Jun 3, 2021 at 13:42
  • @AndriyM - my point is that you still run into the issue of not knowing which column in the junction table you need to check. You can resolve this as you have below (thanks again!), so I guess you just end up with two different queries: one for where the related entities different types and so you can know which column, and one where you don’t know so you have to check both. Alas - even if that is the better route, I’ve inherited the database so backfilling the columns would be required!
    – mactreb
    Commented Jun 3, 2021 at 16:31

2 Answers 2

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
;
0
0

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 )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.