I need some simple help - 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

I need some simple help

Author

Topic

Zanzibarjones
Starting Member

6 Posts

Posted - 2011-10-06 : 19:52:46

I have a db where I am trying to calculate fields in 2 tables based on a field within one of those tables.

Table1
Field1
Field2
Field3 (Dropdown List to Table2.Field1)

Table2
Field1
Field2

Query1:
I want to calculate like this. Multiply Table1.Field2 * Table2.Field2 Where Table1.Field3 = Table2.Field1 AS "Calculate Value"

Any help is much appreciated :)

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-10-06 : 20:06:26

select T1.Field2 * T2.Field2 AS "Calculate Value"
from table1 t1
join table2 t2 on T11.Field3 = T2.Field1

Likes to run, hates the runs!

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-10-06 : 20:07:01

t1.field3 not t11.

Likes to run, hates the runs!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 02:07:48

one question to ask here is any of fields Field2 in table1 and table2 is nullable.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Zanzibarjones
Starting Member

6 Posts

Posted - 2011-10-07 : 09:33:39

@visakh16: What do you mean nullable? Will any of them hold nothing?
No. All the fields that need calculating will ALWAYS have data in them.

Zanzibarjones
Starting Member

6 Posts

Posted - 2011-10-07 : 09:38:18

@pduffin:
"select T1.Field2 * T2.Field2 AS "Calculate Value"
from table1 t1
join table2 t2 on T11.Field3 = T2.Field1:

Line 2: what is t1?
Line 3: what is t2?
Are those the fields?

Zanzibarjones
Starting Member

6 Posts

Posted - 2011-10-07 : 10:03:00

Ok well here is what I am really trying to do. If I am not explaining myself well, please say so, and I will provide whatever information is needed.
I am making a simple db to tracking mining for an online game (no not WoW), another one.
Anyway, I have 2 tables.
tbl_Minerals: Which has these 2 fields:
MineralName (text field)
stoneValue: (currency field)

So it looks like this:
tbl_Minerals:
MineralName stoneValue
Mineral1 $0.01
Mineral2 $0.02
Mineral3 $0.02
Mineral4 $0.05
etc for about 30 records.

tbl_Claims:
Longitude Latitude Elevation Depth stoneQty Mineral
100001 900001 421 123m 85 Mineral1
100011 900002 421 225m 15 Mineral3
100211 802130 123 425m 234 Mineral1

tbl_Minerals.MineralName should be linked to tbl_Claims.Mineral.
I can have the same Mineral from tbl_Minerals show up several times in tbl_Claims.Mineral.

Both tables are using the standard ID field as primary keys.
SO I believe that I need a one to many relationship.

Then the query I want is simple, like stated above.
Calculate a value called "TT Value: " by multiplying tbl_Claims.stoneQty * tbl_Minerals.stoneValue for the Mineral that has been selected for that record in tbl_Claims.Mineral.

Does that make more sense?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-07 : 12:30:53

you mean calculate TT value for each Mineral level or each claim level?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Zanzibarjones
Starting Member

6 Posts

Posted - 2011-10-07 : 13:20:35

If I find a claim,
- I want to be able to choose the mineral I found from the drop down list (drop down list's source in that table points to tbl_Mineral.MineralName)
- And based on what I choose in that drop down list, I want it to then take that MineralName, find the record that matches it in tbl_Minerals.MineralName, get the tbl_Minerals.stoneValue data from that record and multiply it to the tbl_Claims.stoneQty data I entered and display it as "TT Value" on the form.

Zanzibarjones
Starting Member

6 Posts

Posted - 2011-10-07 : 13:25:31

I basically want to take whatever rock I find, get it's base price from a table, and calculate the total value of the amount of rocks I found.
I find a claim of copper. In that claim I found 35 rocks of copper. I look at my sheet, down the list till I find copper, move my finger over to the next field, copper sells for .35 a rock. So .25 value * 35 copper rocks = TT Value of $8.75
I find a another claim, this time it's Iron. In that claim I found 5 rocks of Iron. I look at my cheat sheet, scroll down the list, Iron is selling at $1.00 a rock. So $1.00 value * 8 Iron rocks - TT Value $8.00
Does that explain it better?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-08 : 00:53:45

yep much better


declare @mineral varchar(100) 
set @mineral = 'Copper'

select c.Mineral,c.stoneQty * m.stoneValue AS TT
from tbl_Claim c
join tbl_Minerals m
on m.MineralName = c.Mineral
where c.Mineral = @mineral

pass any value for mineral and you will get TT value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources