Search code examples
sqlsql-servert-sqljoinmany-to-many

T-SQL How to select all items without relationship in many-to-many situation?

Let's say there are 3 tables:

enter image description here

How can I show which CustomerId did not make an order for particular SupplierId

For example: SupplierId 2 did not get orders from CustomerId 1 and 5

So far my idea is to create a Table of all possible combinations of CustomerId/SupplierId and rows that have a match in the Orders table. Is there a better way?

Solution

  • You can cross join the two referrential tables to generate all possible combinations, and use not exists to filter on those that do not exists in the bridge table:

    select c.customerId, s.supplierId
from customers c
cross join suppliers s
where not exists (
    select 1 
    from orders o 
    where o.customerId = c.customerId and o.supplierId = s.supplierId
)

    You can also do this with an anti-left join:

    select c.customerId, s.supplierId
from customers c
cross join suppliers s
left join orders o  
    on o.customerId = c.customerId and o.supplierId = s.supplierId
where o.customerId is null