CODEWITHSUNDEEP
sql
Sangamesh Hs
Sangamesh Hs

Reputation: 1447

Looping with SQL statements

I am trying to implement a small logic in SQL:

For example : I have two tables A and B

    A            B
ID  Qnt         ID  Qnt   Value
1    50         1   100   1000      
2   130         2   200   1000
3   180         3   300   1000
4   320         4   400   2000
5   500         5   500   2000
                6   600   2000
                7   700   2000

I would to loop through each value of Qnt in TABLE A and check if the value lie between the range of the values in Qnt of TABLE B and get the corresponding value.

I know how I could achieve this with using While loop. But I don't want to do this since looping affects my query performance significantly. I would like to do this with only SQL statements. Can anyone suggest an idea how I could go with this? just an idea would be great! Any sql would be fine, I would like to know just the logic.

The output would look like :

Output
ID      Qnt     Value
1       50      1000
2       130     1000
3       180     1000
4       320     2000
5       500     2000

Thanks

Upvotes: 1

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

This is a lookup. You can do it with a correlated subquery, although the syntax is a bit different in the two databases. Here is the MySQL version:

select a.*,
       (select b.value
        from b
        where b.qnt <= a.qnt
        order by b.qnt desc
        limit 1
       ) as value
from a;

Here is the SQL Server version:

select a.*,
       (select top 1 b.value
        from b
        where b.qnt <= a.qnt
        order by b.qnt desc
       ) as value
from a;

Upvotes: 7

Related Questions