SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Joining two unrelated tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kaustubh
Starting Member

India
4 Posts

Posted - 06/13/2005 :  10:50:16  Show Profile  Reply with Quote
Hey!

I have a view which returns me a column (lets say colA) with values like 0.85, 1.95, 1.09 etc.

Then i have a table which contains a RangeId's for colA values, with ranges like..

RangeID colA_UpperLimit colA_LowerLimit

01 0.50 1.50
02 1.51 2.01
03 2.02 3.52

How do i get the colA values with the corresponding RangeId's in one view?
There is no relation between the two tables.

I have already tried the following:

SELECT T.RangeID, V.ColA
FROM viewColA V
LEFT JOIN tableRange T ON V.ColA BETWEEN T.colA_UpperLimit AND T.colA_LowerLimit

I get a voilation error.

Thanks everyone!



There is no unanswered reply!

X002548
Not Just a Number

15586 Posts

Posted - 06/13/2005 :  11:08:37  Show Profile  Reply with Quote
I'm not sure I follow.

Follow the link below for a hint on how to post.



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 06/13/2005 :  11:21:23  Show Profile  Visit jhermiz's Homepage  Reply with Quote
CREATE TABLE [dbo].[ v ] ( --had to edit by the v because of smiley face, just get rid of the spaces before and after the v.
[ColA] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Range] (
[RangeID] [int] IDENTITY (1, 1) NOT NULL ,
[U] [decimal](18, 2) NULL ,
[L] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

INSERT INTO v(ColA)
VALUES(0.90)

INSERT INTO v(ColA)
VALUES(5.90)

INSERT INTO v(ColA)
VALUES(10.90)

INSERT INTO v(ColA)
VALUES(3.90)

INSERT INTO Range(U, L)
VALUES(0, 1)

INSERT INTO Range(U, L)
VALUES(2, 5)

INSERT INTO Range(U, L)
VALUES(6, 100)

SELECT v.ColA, t.RangeID FROM v JOIN RANGE t
ON v.ColA BETWEEN t.U AND t.L




Keeping the web experience alive -- http://www.web-impulse.com
Imperfection living for perfection --
http://jhermiz.blogspot.com/

Edited by - jhermiz on 06/13/2005 11:24:41
Go to Top of Page

kaustubh
Starting Member

India
4 Posts

Posted - 06/13/2005 :  11:36:18  Show Profile  Reply with Quote
Hi Jon(hope I got that right),

Thanks for your response, but it is pretty much what I stated in my query! I already have a view wherein I am calulating values, and a table where in I check for the range id depending on the upper and the lower limit. I wanted to know how I could do so.

Thanks again,
Kaustubh




quote:
Originally posted by jhermiz

CREATE TABLE [dbo].[ v ] ( --had to edit by the v because of smiley face, just get rid of the spaces before and after the v.
[ColA] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Range] (
[RangeID] [int] IDENTITY (1, 1) NOT NULL ,
[U] [decimal](18, 2) NULL ,
[L] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

INSERT INTO v(ColA)
VALUES(0.90)

INSERT INTO v(ColA)
VALUES(5.90)

INSERT INTO v(ColA)
VALUES(10.90)

INSERT INTO v(ColA)
VALUES(3.90)

INSERT INTO Range(U, L)
VALUES(0, 1)

INSERT INTO Range(U, L)
VALUES(2, 5)

INSERT INTO Range(U, L)
VALUES(6, 100)

SELECT v.ColA, t.RangeID FROM v JOIN RANGE t
ON v.ColA BETWEEN t.U AND t.L




Keeping the web experience alive -- http://www.web-impulse.com
Imperfection living for perfection --
http://jhermiz.blogspot.com/




There is no unanswered reply!
Go to Top of Page

jhermiz
Flowing Fount of Yak Knowledge

USA
3564 Posts

Posted - 06/13/2005 :  11:47:00  Show Profile  Visit jhermiz's Homepage  Reply with Quote
So if you have this stuff why doesnt it work? Your join is different..take what I posted copy and paste it into SQL Query Analyzer and run it. You will see the expected results.

Jon



Keeping the web experience alive -- http://www.web-impulse.com
Imperfection living for perfection --
http://jhermiz.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000