Table t1 contains data of those product which is not available to those retailer for a given company while table t2 contains master data of retailer-item and its availability status.
I want to write a sql query to find out- those items which is not available to any retailer (i.e. in table t1) , we have to find is there any other retailer who can provide the same item for that same company(i.e. in table t2).
Edit:- { Notes:- For a given company , there are many retailers and if any item which we are finding is available to more than 1 retailer then we will look according to ascending order of retailer .
Ex:-
2.In t1- retailer b and product b4 - it is available in both a and c in t2 but for retailer- a currently its availability is not there, so, the output should contain retailer c. }
Above condition is changed
Now, For a given company , there are many retailers and if any item which we are finding is available to more than 1 retailer then we will choose those retailer who has most quantity of that item available and for that i have one more column in table t2 which shows the available quantity
** Insert Sql Script**
t1:-
CREATE TABLE t1
(
company VARCHAR(512),
retailer VARCHAR(512),
product VARCHAR(512),
availability VARCHAR(512)
);
INSERT INTO t1 (company, retailer, product, availability) VALUES
('1', 'a', 'a1', '0'),
('1', 'a', 'a2', '0'),
('1', 'a', 'a3', '0'),
('1', 'b', 'b1', '0'),
('1', 'b', 'b2', '0'),
('1', 'b', 'b3', '0'),
('1', 'b', 'b4', '0'),
('1', 'c', 'c1', '0'),
('1', 'c', 'c2', '0');
t2:-
CREATE TABLE t2
(
company VARCHAR(512),
retailer VARCHAR(512),
product VARCHAR(512),
availability VARCHAR(512)
);
INSERT INTO t2 (company, retailer, product, availability) VALUES
('1', 'a', 'a1', '0'),
('1', 'a', 'a2', '0'),
('1', 'a', 'a3', '0'),
('1', 'a', 'b2', '1'),
('1', 'a', 'b4', '0'),
('1', 'a', 'c1', '1'),
('1', 'a', 'b3', '0'),
('1', 'b', 'b1', '0'),
('1', 'b', 'b2', '0'),
('1', 'b', 'b3', '0'),
('1', 'b', 'b4', '0'),
('1', 'b', 'a3', '1'),
('1', 'b', 'c2', '1'),
('1', 'b', 'c1', '1'),
('1', 'c', 'c1', '0'),
('1', 'c', 'c2', '0'),
('1', 'c', 'a1', '1'),
('1', 'c', 'b2', '1'),
('1', 'c', 'b1', '1'),
('1', 'c', 'b4', '1'),
('1', 'c', 'a3', '1');
** Sample Data**
t1:-
| company | retailer | product | availability |
|---|---|---|---|
| 1 | a | a1 | 0 |
| 1 | a | a2 | 0 |
| 1 | a | a3 | 0 |
| 1 | b | b1 | 0 |
| 1 | b | b2 | 0 |
| 1 | b | b3 | 0 |
| 1 | b | b4 | 0 |
| 1 | c | c1 | 0 |
| 1 | c | c2 | 0 |
t2:-
| company | retailer | product | availability |
|---|---|---|---|
| 1 | a | a1 | 0 |
| 1 | a | a2 | 0 |
| 1 | a | a3 | 0 |
| 1 | a | b2 | 1 |
| 1 | a | b4 | 0 |
| 1 | a | c1 | 1 |
| 1 | a | b3 | 0 |
| 1 | b | b1 | 0 |
| 1 | b | b2 | 0 |
| 1 | b | b3 | 0 |
| 1 | b | b4 | 0 |
| 1 | b | a3 | 1 |
| 1 | b | c2 | 1 |
| 1 | b | c1 | 1 |
| 1 | c | c1 | 0 |
| 1 | c | c2 | 0 |
| 1 | c | a1 | 1 |
| 1 | c | b2 | 1 |
| 1 | c | b1 | 1 |
| 1 | c | b4 | 1 |
| 1 | c | a3 | 1 |
expected output
| company | retailer | product | availability | corresponding_retailer | product_availability |
|---|---|---|---|---|---|
| 1 | a | a1 | 0 | c | 1 |
| 1 | a | a2 | 0 | NA | NA |
| 1 | a | a3 | 0 | b | 1 |
| 1 | b | b1 | 0 | c | 1 |
| 1 | b | b2 | 0 | a | 1 |
| 1 | b | b3 | 0 | a | 0 |
| 1 | b | b4 | 0 | c | 1 |
| 1 | c | c1 | 0 | a | 1 |
| 1 | c | c2 | 0 | b | 1 |
** what I have tried**
with cte1 as (select * from t2 where availability=1)
select * from cte1 left join t1 on cte1.product=t1.product
I am getting issue when same product is available in multiple retailer , so basically not able to iterate over one retailer at a time then move to other if not found and so.
FYI- My database support PostgreSQL.
Please help me out, Thanks.
You can left join table t2 onto t1, find the nearest matching product, and then join that product's availability back to the result:
select t2.*, t3.availability from (
select t.company, t.retailer, t.product, t.availability, min(t1.retailer) c_retailer
from t1 t left join t2 t1 on t.product = t1.product and t1.availability::int > 0
group by t.company, t.retailer, t.product, t.availability) t2
left join t2 t3 on t3.retailer = t2.c_retailer and t2.product = t3.product
order by t2.company, t2.retailer, t2.product