Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-13
3
Medium Priority
?
227 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
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 1000 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 1000 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 your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Screencast - Getting to Know the Pipeline

886 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