Range of a field depending in a related table ms sql 2995

I have a table a with numeric values

1
3
5
6
12
34
54
16

And another
0
10
20
30
40
I need a query to give me in wich ranga the values of table 1 fall.

for example, 6 relates to record 1 (0<=6<10) 31 relates to record 4 (30<=31<40)
robrodpAsked:
Who is Participating?
 
JoeNuvoConnect With a Mentor Commented:
replace Table1, fieldname1, Table2, fieldname2 with your table name and fieldname accordingly.

Declare @temp TABLE (rid int IDENTITY(1,1) PRIMARY KEY, data int)
INSERT INTO @temp (data)
SELECT fieldname2 FROM Table2
ORDER BY fieldname2

SELECT T.fieldname1, A.rid, A.Data, B.Data
FROM
	@temp A
	INNER JOIN @temp B ON A.rid + 1 = B.rid
	INNER JOIN Table1 T ON T.fieldname1 >= A.Data AND T.fieldname1 < B.Data

Open in new window

0
 
ThomasianConnect With a Mentor Commented:
SELECT t1.*, range = (SELECT MAX(t2.fieldname) FROM tableB t2 WHERE t1.fieldname>=t2.fieldname)
FROM tableA t1

Open in new window

0
 
ThomasianCommented:
If you need the whole record from tableB, you can use outer apply
SELECT t1.*, t2.*
FROM tableA t1 OUTER APPLY
    (SELECT TOP 1 *
       FROM tableB
       WHERE t1.fieldname>=fieldname
       ORDER BY fieldname DESC
     ) t2

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.