'Postgresql query to compare tables and find inside and outside results

I currently have a pair of datasets. They are closely related with each other in that they share a common column called product.

So the first table would look like this (provider table)

Provider Product
John Apples
John Bananas
Steve Apples
Steve Pears

The second table would look like this (buyer table)

Buyer Product
Elton Apples
Elton Kiwis
Austin Bananas
Austin Melons

The relationship that I need is on how the products that every provider has, relates to the products that every buyer also owns. Or in a simpler manner, what are the products a provider could sell to a buyer and which are the ones he is missing, with a focus on what are the products the buyer requires.

For instance, the relationship of the current users in the table would tell me

  1. John -> Elton = Apples(Could Sell), Kiwis (Missing)
  2. John -> Austin = Bananas(Could Sell), Melons (Missing)
  3. Steve -> Elton = Apples(Could Sell), Kiwis (Missing)
  4. Steve -> Austin = Bananas(Missing), Melons (Missing)

The idea is to end un with a table that looks like this, and that contains every provider, along with every relationship to every buyer, ordered first by providers, and then by buyers.

Provider Buyer Selling product Missing Product
John Elton Apples Kiwis
John Austin Bananas Melons
Steve Elton Apples Kiwis
Steve Austin ---- Bananas, Melons

I have used this type of queries to try to find the products that match but it is not working as intended

from (
    select tss.product , buyer_table.buyer
    from buyer_table
    inner join provider_table tss
    on buyer_table.product = tss.product
    group by tss.provider , buyer_table.buyer 
) t join buyer_table c on t.buyer= c.buyer
order by t.provider desc;

Additionally, I have not being able to find a way of finding the products that do not match and have it grouped by Provider and Buyer.

It is ok to build this use case by parts, for instance find the products that match and dont match first, and then join them together, or do it all in a single query.

What are the SQL queries that I need in order to create this type of functionality?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source