Three (3) Table Join 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

SQL Server 2005 Forums

Transact-SQL (2005)

Three (3) Table Join Question

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2009-11-01 : 09:51:53

I've got three tables

-- table ResourceCapacity
date ResourceID Capacity
1/1/01 1 8
1/2/01 1 0
1/3/01 1 0
1/4/01 1 8

-- Table DailyAssignments
date AssignmentID Work
1/1/01 1 6
1/2/01 1 0
1/3/01 1 0
1/4/01 1 6

-- Table Assignments
AssignmentID ResourceID
1 1

--I want to return the following

date AssignmentID Work Capacity
1/1/01 1 6 8
1/2/01 1 0 0
1/3/01 1 0 0
1/4/01 1 6 8

Help much appreciated!!

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-11-01 : 23:27:42

You have not specified the relation ship between ResourceCapacity and DailyAssignments.It Seems that Assignments is the bridging table. But without a date column in Assignments table it will result in many to many relationship. I hope you are trying to get something like below..

create table ResourceCapacity(date datetime,ResourceID int,Capacity int)
insert ResourceCapacity(date,ResourceID,Capacity) Values ('01 Jan 2001',1,8)
insert ResourceCapacity(date,ResourceID,Capacity) Values ('02 Jan 2001',1,0)
insert ResourceCapacity(date,ResourceID,Capacity) Values ('03 Jan 2001',1,0)
insert ResourceCapacity(date,ResourceID,Capacity) Values ('04 Jan 2001',1,8)

create Table DailyAssignments(date datetime,AssignmentID int,Work int)
Insert DailyAssignments(date,AssignmentID,Work) Values('01 Jan 2001',1,6)
insert DailyAssignments(date,AssignmentID,Work) Values('02 Jan 2001',1,0)
insert DailyAssignments(date,AssignmentID,Work) Values('03 Jan 2001',1,0)
insert DailyAssignments(date,AssignmentID,Work) Values('04 Jan 2001',1,6)

create Table Assignments (AssignmentID int,ResourceID int)
insert Assignments values(1,1)

Select
DA.*,R.Capacity
from
DailyAssignments DA,
Assignments A,
ResourceCapacity R
Where
DA.AssignmentID=A.AssignmentID AND
DA.date=R.date AND
A.ResourceID=R.ResourceID

matrixmind
Starting Member

9 Posts

Posted - 2009-11-02 : 00:29:33

quote:
Originally posted by JohnBGood

I've got three tables

-- table ResourceCapacity
date ResourceID Capacity
1/1/01 1 8
1/2/01 1 0
1/3/01 1 0
1/4/01 1 8

-- Table DailyAssignments
date AssignmentID Work
1/1/01 1 6
1/2/01 1 0
1/3/01 1 0
1/4/01 1 6

-- Table Assignments
AssignmentID ResourceID
1 1

--I want to return the following

date AssignmentID Work Capacity
1/1/01 1 6 8
1/2/01 1 0 0
1/3/01 1 0 0
1/4/01 1 6 8

Help much appreciated!!

with abc
as
(
select D.Date,D.AssignmentID,Work,A.ResourceID
From Assignments A inner join DailyAssignments D
on A.AssignmentID=D.AssignmentID
)
select A.Date,AssignmentID,Work,Capacity From abc A inner join ResourceCapacity C
on A.ResourceID=C.ResourceID and A.Date=C.Date

Dinesh Sharma
Matrix Solution
Sr.Software Engg.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-02 : 02:03:00

slight modification to sanjov query
try to use inner join than cross join
SELECT
DA.*,R.Capacity
FROM DailyAssignments DA
INNER JOIN Assignments A on a.AssignmentID = da.AssignmentID
INNER JOIN ResourceCapacity R on r.ResourceID = a.ResourceID and da.date = r.date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources