0

Can anyone help? I have been fumbling around with a SQL statement for days trying to make this work. I am looking for a Statement that will output a table based on an incremented number.

Example Table1:
Value1.......Value2.......Incremented Number
14...............null.............1
12..............15...............2
12...............98..............3
15...............14..............4
17...............15..............5
1.................14..............6
5.................98..............7
Example Table2:
Value1.........Value2
X030............1
X842............5
M123............7

Example Output (For all Incremented Number values between Table2.Value2=1 and Table2Value2=5 add X030, etc.):

Value1.......Value2.......Incremented Number
14...............null.............1.....................X030
12..............15...............2......................X030
12...............98..............3......................X030
15...............14..............4......................X030
17...............15..............5......................X842
1.................14..............6......................X842
5..................98..............7.....................M123
etc. etc.

I have tried a bunch of different SELECT Statements using the max function in conjunction with a less than WHERE statement but, am completely missing the mark.

Thanks for your help!
Mark

3
Contributors
3
Replies
4
Views
9 Years
Discussion Span
Last Post by msi
0
select a.value1, b.value1, a.incremented_no, b.value2
from table1 a, table2 b
where a.incremented_no = b.incremented_no
and b.incremented_no between 1 and 5
order by a.incremented_no

This is what i got from your post O_o ..
Show us your SQL statement ..

0

This is what i got from your post O_o ..
Show us your SQL statement ..

My SQL statment would look like:

SELECT table1.value1, table1.value2, table2.value1
FROM table1, table2
WHERE table1.[increment number] > table2.[Value2] (here is the part I can't figure out) AND table2.[Value2] is closest to table1.increment number;

So, for:
Example Table2:
Value1.........Value2
X030............1 (All table1.Increment Numbers from 1 to 4 are assignmed X030)
X842............5 (All table1.Increment Numbers from 5 to 6 are assigned X842)
M123............7 (All table1.Increment Numbers from 7 until the next Value2 are assignmed M123)

Hopefully this clears it up a little.

Thanks!

0

select *,(select value1 from table2 where value2=(select max(value2) from table2 t2 where t2.value2<= t1.IncrementedNumber)) from table1 t1

You can get good practice in SQL at SQL Exercises

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.