0

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?

GMB
  • 216,147
  • 25
  • 84
  • 135
AnatoliiV
  • 21
  • 4
  • 1
    Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. This is a faq. Before considering posting please read the manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers.Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy Apr 18 '20 at 10:07
  • Does this answer your question? [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) – philipxy Apr 18 '20 at 10:09

1 Answers1

1

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
GMB
  • 216,147
  • 25
  • 84
  • 135