Find smallest value connected with arrays in 2 tables SQL Postgres

Sebastian Halik 0
Viewed 35

I have table A with columns id (integer), group (_int8 -> array) and table B with id (int), group (_int8 -> array) and price (money). I have to find the smallest amount of money for each value in array in table A. For example:

Table A :

id | group
---|-------
 0 | {3,4,5}
 1 | {3,6}

Table B :

id | group |price
---|-------|------
5| {1}|100
6| {3,4}| 200
7| {3,5}| 300
8| {4,6}| 100

So for first record (id 0) i check money with first value (3) in all records containing that value. So it's with id 6 and 7. Next I have to return smallest of them so it's 200 (6). Next i do the same for second value (4) and it's 100, next is 5 and money is 300.

Next another row (id 1) so for 3 it's 200 and for 6 it's 100.

Now i have to sum all of that -> 200 + 100 + 300 +200 + 100 = 900

How can i do it in postgres?

sql postgresql arrays
1 Answers

Here it is. Plese note that table a is first normalized (or flattened) using a lateral join.

select sum(minprice) from
(
 select v,
   (select min(price) from b where v = any("group")) as minprice
 from a cross join lateral unnest("group") as af(v)
) as t;
Stefanov.sm0
Related