CODEWITHSUNDEEP
sqlsql-serversql-server-2008selectjoin
user6576015
user6576015

Reputation: 45

SQL SERVER 2008 - joins

I have three tables that I'm trying to join to get the necessary data. Here they are...

*TblComp*        *TblCompParent*         *tblCompProcesses*
CompID            CompBillingID           CompID
CompBillingID     Capacity1               CompProcessID
                  Capacity2

So what I'm trying to do with these three tables is....

Select tblCompParent.Capacity1, tblCompParent.Capacity2, CompProcessID

My problem is this...In tblComp there are 351 values - so i start off by joining tblComp and tblCompparent and SELECTING tblCompparent.capacity1, tblcompparent.capacity2, the query looks like this...

SELECT dbo.tblComp.CompID, dbo.tblCompParents.Capacity1, dbo.tblCompParents.Capacity2
FROM dbo.tblCompParents INNER JOIN
dbo.tblComp ON dbo.tblCompParents.CompBillingID = dbo.tblComp.CompBillingID

And this works fine, it's when I try to join tblCompProcess to pull the CompProcessID is when I get like 580 records. I'm not sure what can of join I have to do on tblCompprocess to select only one CompProcessID per compID.

And it seems like i have to use tblComp otherwise I'll have no way of joining tblCompProcess.

EDIT1:

SELECT dbo.tblComp.CompID, dbo.tblCompParents.Capacity1, dbo.tblCompParents.Capacity2, tblCompProcess.compprocessID
FROM dbo.tblCompParents INNER JOIN
dbo.tblComp ON dbo.tblCompParents.CompBillingID = dbo.tblComp.CompBillingID
Inner Join dbo.tblCompprocess on tblCompProcess.CompID = tblComp.CompID

Upvotes: 0

Views: 48

Answers (1)

Hogan
Hogan

Reputation: 70513

You don't show us the whole data model so I don't know exactly what is going on but clearly tblCompProcesses has more than one row in your join. I would fix it like this:

SELECT dbo.tblComp.CompID, dbo.tblCompParents.Capacity1, dbo.tblCompParents.Capacity2, x.compprocessID
FROM dbo.tblCompParents
INNER JOIN dbo.tblComp ON dbo.tblCompParents.CompBillingID = dbo.tblComp.CompBillingID
INNER JOIN (SELECT DISTINCT CompID, compprocessID
            FROM dbo.tblCompprocess) X on x.CompID = tblComp.CompID

Upvotes: 1

Related Questions