'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
- John -> Elton = Apples(Could Sell), Kiwis (Missing)
- John -> Austin = Bananas(Could Sell), Melons (Missing)
- Steve -> Elton = Apples(Could Sell), Kiwis (Missing)
- 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 |
|---|
