CODEWITHSUNDEEP
sqlpostgresql
hectorviov
hectorviov

Reputation: 77

Use column from first table as condition for joining second table

Suppose I have products:

folio   price     quantity
1      100.00       1
1      450.00       2
3      150.00       1
4      600.00       2

terms:(to know how many payment terms depending on the price of the product)

level    term
0.01       12
100.00     14
200.00     16
300.00     18
400.00     20
500.00     22

What can I do to have a resulting table like this:

folio    price    quantity     term
1        100.00   1            14
1        450.00   2            20

I've tried using:

SELECT a.*, b.term 
FROM products AS a 
JOIN terms AS b ON b.level <= a.price 
WHERE a.folio = 1

But I end up getting:

folio price   quantity   term
1     100.00  1          12
1     100.00  1          14
1     450.00  2          12
1     450.00  2          14
1     450.00  2          16
1     450.00  2          18
1     450.00  2          20

What can I do so I only get the row with the biggest term? Please help!

Upvotes: 1

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You are looking for one row from the terms table, not all of them. One way to do this is with a correlated subquery:

SELECT p.*,
       (select t.term from terms t where p.price >= t.level order by t.level desc limit 1
       ) as term
FROM products p 
WHERE p.folio = 1;

If you can modify your terms table to have a minimum and maximum price, then that would make it easier to user.

And, you can mimic this with the lead() function:

select p.*, t.term
from products p left outer join
     (select t.*, lead(level) over (order by level) as nextlevel
      from terms t
     ) t
     on p.price >= t.level and (p.price < t.nextlevel or t.nextlevel is null)
where p.folio = 1;

Upvotes: 1

Related Questions