Joining two unrelated tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Joining two unrelated tables

Author

Topic

kaustubh
Starting Member

4 Posts

Posted - 2005-06-13 : 10:50:16

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 - 2005-06-13 : 11:08:37

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

jhermiz

3564 Posts

Posted - 2005-06-13 : 11:21:23

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 -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

kaustubh
Starting Member

4 Posts

Posted - 2005-06-13 : 11:36:18

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 -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

There is no unanswered reply!

jhermiz

3564 Posts

Posted - 2005-06-13 : 11:47:00

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 -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources