Sub Query Problem - 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

General SQL Server Forums

New to SQL Server Programming

Sub Query Problem

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2010-07-01 : 12:45:53

I have 2 tables......

CREATE TABLE [dbo].[table1](
[ITEM] [char] (31) NOT NULL,
[SEQ] [char](11) NOT NULL,
[TIME] [numeric] (19,5) NULL,
[CODE] [char] (11) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[table2](
[CODE] [char] (11) NOT NULL,
[RATE] [numeric](19, 5) NOT NULL,
) ON [PRIMARY]

Sample Data -

insert into table1 values ('ITEM A', '10', '1.0', 'TEN')
insert into table1 values ('ITEM A', '20', '2.0', 'TWELVE')
insert into table1 values ('ITEM B', '10', '3.0', 'FIVE')
insert into table1 values ('ITEM C', '10', '.75', 'FIVE')
insert into table1 values ('ITEM C', '20', '2.0', 'TEN')
insert into table1 values ('ITEM C', '30', '1.0', 'TWELVE')
insert into table2 values ('TEN', '10.00000')
insert into table2 values ('FIVE', '5.00000')
insert into table2 values ('TWELVE', '12.00000')

table 1 - looks like
Item SEQ Time Code
ITEM A 10 1.00000 TEN
ITEM A 20 2.00000 TWELVE
ITEM B 10 3.00000 FIVE
ITEM C 10 0.75000 FIVE
ITEM C 20 2.00000 TEN
ITEM C 30 1.00000 TWELVE

Table2 Looks like -
Code Rate
TEN 10.00000
FIVE 5.00000
TWELVE 12.00000

Table 1 has a unique record for Item and SEQ and a CODE which I need to join with Table2. I want to create a view that simply adds a calculated value to table1 using the Rate in table2 for the code in table1 as such:

Results should be.....

Item seq time code cost
ITEM A 10 1.00 TEN 10.00000
ITEM A 20 2.00 TWELVE 24.00000
ITEM B 10 3.00 FIVE 15.00000
ITEM C 10 0.75 FIVE 3.75000
ITEM C 20 2.00 TEN 20.00000
ITEM C 30 1.00 TWELVE 12.00000

I know I need some kind of nested query or sub query......

Any advice would be appreciated. I'm relatively new to transact sql - still learning the more complicated data conditions....

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-01 : 12:52:52

So You are joinging on code?

CREATE VIEW <viewname>
AS

SELECT *, a.Code * b.Rate AS CalcCol
FROM table1 a JOIN table2 b
ON a.Code = b.Code

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

Add yourself!
http://www.frappr.com/sqlteam

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 12:53:45

There is no need for sub query a simple join is enough.



select t1.*,t1.time * t2.rate as cost from table1 t1 
inner join table2 t2 on t1.code=t2.code

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 12:56:47

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

btamulis
Yak Posting Veteran

64 Posts

Posted - 2010-07-01 : 13:02:13

Thank you very much.

I had tried a traditional Join but had been unable to make it work.

I had to change your formula (time x rate) and it now works..

Thanks

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 13:07:35

Welcome .

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-01 : 13:08:37

quote:
Originally posted by Idera

There is no need for sub query a simple join is enough.

select t1.*,t1.time * t2.rate as cost from table1 t1
inner join table2 t2 on t1.code=t2.code

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

BRILLIANT!!!!!!!

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

Add yourself!
http://www.frappr.com/sqlteam

Sachin.Nand

2937 Posts

Posted - 2010-07-01 : 13:11:12

quote:
Originally posted by X002548

quote:
Originally posted by Idera

There is no need for sub query a simple join is enough.

select t1.*,t1.time * t2.rate as cost from table1 t1
inner join table2 t2 on t1.code=t2.code

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

BRILLIANT!!!!!!!

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

Add yourself!
http://www.frappr.com/sqlteam

And why is that?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-07-01 : 14:00:43

Because it is the exact same thing that he posted. He may now call YOU brilliant and look modest, when he is really tooting his own horn.

<3 ya Brett

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources