SQl Query - Please 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

SQL Server 2008 Forums

Transact-SQL (2008)

SQl Query - Please help

Author

Topic

Jamuna
Starting Member

7 Posts

Posted - 2012-07-03 : 03:38:01

Hi All,
Can anybody help me in writing an sql query which is defined below:
I'm having two table Itemtype and Items
Itemtype
-------------------
ItemTypeID ItemParent TypeName
-------------------------------
1 0 XXX
2 1 YYY
3 1 ZZZ
4 0 SSS
5 4 GGG

Items
--------------------
ItemID ItemTypeID ItemCost
----------------------------------
1 1 5000
2 2 1000
3 4 250
4 1 1655

These are the two tables:I joined both table using itemtypeid.
Now i need to display Items with ItemParentId = 0
and
calculate the Itemcost of Items with itemtypeId and ItemparentTypeId
value same.
Please help me

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-07-03 : 04:31:37

Hi
not sure , but here is something

select items.*,COST.Cost
from items
inner join
(
select itemtype.typeName,itemtype.itemTypeID,sum(itemCost) as Cost
from itemtype
inner join items on itemtype.itemTypeID=items.itemTypeID
where itemParent=0
group by itemtype.typeName,itemtype.itemTypeID)COST on items.itemTypeID=COST.itemTypeID

or

select itemtype.typeName,itemtype.itemTypeID,sum(itemCost) as Cost
from itemtype
inner join items on itemtype.itemTypeID=items.itemTypeID
where itemParent=0
group by itemtype.typeName,itemtype.itemTypeID

or

select sum(itemCost) as Cost
from itemtype
inner join items on itemtype.itemTypeID=items.itemTypeID
where itemParent=0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources