3 Tables without relation (need result)

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)

3 Tables without relation (need result)

Author

Topic

javad.nikoo
Starting Member

17 Posts

Posted - 2010-10-27 : 04:54:17

Hi
I have three table the First is Store(it contains Stuff,stCountInStore),second is Request Table(StuffReq,ReqCount)and the last one is OrderTable(StufOrd,OrdCount)(In This Table OrdCount added to StCountInStore For Each Stuf)
the relation between is Stuf and there is no relation bet RequstTable AND OrderTabl,
an example of Data :
Store :
A 100
B 150
C 15
D 90
Request:
A 10
B 7
A 13
A 12
A 11
B 9
D 17
B 25
Oreder:
A 1
B 6
A 3
A 20
A 9
B 3
D 16
B 17
OutPut Must Be This:
A (OrdCount(A)in Order +(stCountInStore(A))Store) - sum(A)in Request
B (OrdCount(B)in Order +(stCountInStore(B))Store) - sum(B)in Request
C (OrdCount(C)in Order +(stCountInStore(C))Store) - sum(C)in Request
D (OrdCount(D)in Order +(stCountInStore(D))Store) - sum(D)in Request

I have Written a Query For this But it Must Be Faster than this query
(I SUM(Count)For Each Item In Each TAble And Compute This Formula but its too Expensive)

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-27 : 04:59:09

quote:

I have Written a Query For this But it Must Be Faster than this query
(I SUM(Count)For Each Item In Each TAble And Compute This Formula but its too Expensive)

Can you show us the query you have tried.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-27 : 05:14:07

SELECT s.[Stuff], (s.stCountInStore + isnull(o.OrdCount,0)) - isnull(r.ReqCount,0) as Result
FROM Store as s
LEFT JOIN (SELECT StuffOrd, SUM(OrdCount) as OrdCount FROM OrderTable GROUP BY StuffOrd) as o
ON o.StuffOrd = s.[Stuff]
LEFT JOIN (SELECT StuffReq, SUM(ReqCount) as ReqCount FROM RequestTable GROUP BY StuffReq) as r
ON r.StuffReq = s.Stuff

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

javad.nikoo
Starting Member

17 Posts

Posted - 2010-10-27 : 05:16:22

what i explained was a sample of my table
Each table IS Master\Detail Table BUT I explain this to refuse confusing

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-27 : 05:34:26

Did you try my solution?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources