newbie question - 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

newbie question

Author

Topic

mvc101
Starting Member

3 Posts

Posted - 2011-06-01 : 10:30:06

hi all,

how would i set a field value based upon a value in another table?

i am trying to create a report in ssrs and need to set various fields in the report to a value based on a value in a second table.

for example, let say i have a the following query from one table;

SELECT [ID]
,[RevA]
,[RevB]
FROM [example]

values in the RevA and RevB fields are like "129", "150", "39"...just a set of numbers.

those numbers in the two fields have a rate associated with them.

the rates are in a second table. the two tables can be associated by the field "Code".

how would i create a simple report that displays the query below

SELECT [ID]
,[RevA]
,[RevB]
FROM [example]

but add in the two additional fields: one for RevArate and one for RevBrate --the values from the second table.

Or if i can do this in an expression in ssrs during the report design that would be ok too.

thanks so much..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-01 : 10:33:40

just need a join


Select 
	A.Id,
	A.RevA,
	A.RevB,
	B.RevARate,
	B.RevBRate
From example A
Left Join secondTable B
On A.Code = B.Code

Corey

I Has Returned!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 10:35:52

You just need to join the tables
SELECT e.[ID]
,e.[RevA]
,e.[RevB]
,r.RevARate
,r.RevBRate
FROM [example] as e
INNER JOIN rateTable r
ON e.Code = r.Code

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-01 : 10:37:19

select
t1.id,
t1.RevA,
t1.RevB,
t2.RevArate,
t2.RevBrate
from example as t1
join rates as t2 on t1.code = t2.code

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-01 : 10:37:46

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 10:41:42

I was two minutes late on Corey and you were two minutes late on me. Let's face it, we're just getting old and slow.

Jim

Everyday I learn something that somebody else already knew

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-01 : 10:50:30

quote:
Originally posted by jimf

I was two minutes late on Corey and you were two minutes late on me. Let's face it, we're just getting old and slow.

Jim

Everyday I learn something that somebody else already knew

... and we already have much more forgotten than the younger mates ever have known

No, you're never too old to Yak'n'Roll if you're too young to die.

mvc101
Starting Member

3 Posts

Posted - 2011-06-01 : 11:25:23

let me share what i have more clearly....

table 1:

ID
RevA
RevB

table 2:

Code
Rate

The values in "RevA" and values in "RevB" can be different. Whatever the values in these two fields however correspond to a "Code" in table 2. I need the "Rate" value added to the report. Does that clear things up? I appreciate your assistance thus far.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 11:39:05

.. and now for seomthing completely different.

Could you give us the create table statements for each table and some sample data for each table?

THanks,

Jim

Everyday I learn something that somebody else already knew

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-06-01 : 12:09:59

[code]
Select
A.Id,
A.RevA,
A.RevB,
RevARate = B.Rate,
RevBRate = C.Rate
From example A
Left Join secondTable B
On A.RevA = B.Code
Left Join secondTable C
On A.RevB = B.Code

[/code]

... though JimF has the right of it.

Corey

I Has Returned!!

mvc101
Starting Member

3 Posts

Posted - 2011-06-01 : 14:05:08

Thanks to all of you and the speed of the responses. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources