Selec count from 7 tables from fk in another table

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 2005 Forums

Transact-SQL (2005)

Selec count from 7 tables from fk in another table

Author

Topic

Zoultrex
Starting Member

2 Posts

Posted - 2010-12-28 : 08:53:04

[UPDATE]... correction, I mean SUM not Count!!!
I am making a game in which each player can have a build of their gear set.
Each gear set can have up to 7 types of different items.
There are about 50 different type of items that will be unlocked throughout the game as the player goes along.

Some items alter the same stats, for example I can have a ring that adds 20 in health, and also a sword that also adds 20 health and a ring that adds 5 health.
There are about 15 different status to be added and I want to display the overall status of each build.

my tables are similar to those below:

player
---------
id
nickname
build1 (fk to build table)
build2 (fk to build table)
build3 (fk to build table)

build
-----------
id
armourTypeItem (fk to item table)
WeaponTypeItem (fk to item table)
healthTypeItem (fk to item table)
extraItem1 (fk to item table)
extraItem2 (fk to item table)
extraItem3 (fk to item table)
extraItem4 (fk to item table)

item
----------------
id
armour
health
speed
intelligence
..so on with 10+ stats

=====

Not all items will alter all stats, the stats they dont change will be zero filled, so the count dont give an error.

Im not sure this is the best table desing but I think it suits me pretty well because there is nothing else to this, if you think my design is poor feel free to say

So my question again is, how do I sum all the stats for all the 7 items at once, by player

say my player id is 1,
i want to count the sum of build1 stats of player where player.id = 1

[pseudo sql code ahead...]
select player.name count,sum(item.armour) count,sum(item.speed) ...[goes on for all stats]
from player, build, item
where build.armourTypeItem = item.id and build.WeaponTypeItem = item.id ...[goes on for all 7 items in the build]

Zoultrex
Starting Member

2 Posts

Posted - 2010-12-28 : 13:16:06

Solved:
this is the example of the sum of 1 of the stats
--------------------

select sum(armour)
from item
where id In (
select armourTypeItem from build, player where player.build1 = build.id and player.id = 1
union all
select extraItem1 from build, player where player.build1 = build.id and player.id = 1
union all
select extraItem1 from build, player where player.build1 = build.id and player.id = 1
union all
select continues for all attributes of the item table
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources