Mike Johnston Mike Johnston - 2 months ago 7
SQL Question

Return records from table where they have not been used in another table, 3 tables

I need to return all records from NetworkDomain where any of these networks have not yet been assigned to a specific organization (In this case, OrgName1). So in the below example, If I want to return all networks that have not been assigned to OrgName1 (which the result should then be NetName2 and NetName3) how would I do this?
NOTE: I need the networks to be ordered ASC

Table Name = "BusinessOrganizationDomain"

|---------------------|------------------|
| OrgUUID | OrgName |
|---------------------|------------------|
| 1111 | OrgName1 |
|---------------------|------------------|
| 2222 | OrgName2 |
|---------------------|------------------|


Table Name = "BusinessOrganizationAuthorizedNetwork"

| OrgUUID | NetUUID |
|---------------------|------------------|
| 1111 | 5555 |
|---------------------|------------------|
| 2222 | 6666 |
|---------------------|------------------|


Table Name = "NetworkDomain"

| NetUUID | NetName |
|---------------------|------------------|
| 5555 | NetName1 |
|---------------------|------------------|
| 6666 | NetName2 |
|---------------------|------------------|
| 7777 | NetName3 |
|---------------------|------------------|

Answer

One way is NOT EXISTS predicate

SELECT * FROM NetworkDomain nd
WHERE NOT EXISTS (
  SELECT 1 
  FROM BusinessOrganizationAuthorizedNetwork bn 
  INNER JOIN BusinessOrganizationDomain bd 
     ON bd.OrgUUID = bn.orgUUID AND bn.NetUUID = nd.NetUUID AND bd.OrgName = 'OrgName1'
)