Lower and Upper bound in postgresql

Question

We have column A with values 10,20,30 etc and column B with values as 6,12,18,24,etc.Both columns are different in 2 different tables.I want to calculate the upper and low bound for values in col A. e.g. for col A : 20, upper bound = 24 and lower bound =18. We can calculate by using colA<max(col B) as lower bound and colA>min(ColB). Since both table columns are different, I couldn't directly join them in postgresql..

Looking for logic on how to lookup value when we dont have common column

Answer 1

Try this:

SELECT A,
    (SELECT max(B) FROM table_b WHERE B <= table_a.A) as low_bound,
    (SELECT min(B) FROM table_b WHERE B >= table_a.A) as upper_bound
FROM table_a

The second option could be to create a CTE and use a LAG as follows:

WITH cte AS (
    SELECT B AS b1, LAG(B) OVER (ORDER BY B ASC) AS b2 FROM table_b
)
SELECT * FROM cte INNER JOIN table_a ON (a <= b1 AND a >= b2)

In Common Table Expression you will get two numbers, B and the preceding B.

This way for B = 12 you get B1 = 12 and B2 = 6 And so with the next numbers.

Then, using INNER JOIN, you will join them with the A that is between these numbers.