Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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)
0
robrodp
Asked:
robrodp
  • 2
2 Solutions
 
JoeNuvoCommented:
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
 
ThomasianCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now