Solved

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

Posted on 2011-03-13
3
224 Views
Last Modified: 2012-05-11
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
Comment
Question by:robrodp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 250 total points
ID: 35125362
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
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 250 total points
ID: 35125427
SELECT t1.*, range = (SELECT MAX(t2.fieldname) FROM tableB t2 WHERE t1.fieldname>=t2.fieldname)
FROM tableA t1

Open in new window

0
 
LVL 22

Expert Comment

by:Thomasian
ID: 35125432
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question