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

Transact-SQL (2000)

JOIN Question

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2005-07-19 : 19:16:21

I'm having a little trouble producing the results I need joining 3 tables. For each query, one compID from tblCompanies will be selected. I need the results to pull all records
from tblExecutives and tblAwards. There will be multiple records in tblExecutives for each company but only one record for each executive in tblAwards.

I tried this but it isn't working.

(tblCompanies AS C RIGHT JOIN tblExecutives AS E ON C.CompID = E.CompID) RIGHT JOIN tblAwards AS A ON E.ExecutiveID = A.ExecutiveID

tblCompanies
CompID

tblExecutives
ExecutiveID
CompID

tblAwards
ExecutiveID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-19 : 19:21:17

SELECT ColumnListGoesHere
FROM tblCompanies c
INNER JOIN tblExecutives e
ON c.CompID = e.CompID
INNER JOIN tblAwards a
ON e.ExecutiveID = a.ExecutiveID

Tara

evanburen
Posting Yak Master

167 Posts

Posted - 2005-07-19 : 19:52:51

But there are multiple records in tblExecutives for each company which I want all of.

evanburen
Posting Yak Master

167 Posts

Posted - 2005-07-19 : 20:11:19

If I run just this, I get the 6 records I'm looking for (6 executives for one company)

SELECT ColumnListGoesHere
FROM tblCompanies c
INNER JOIN tblExecutives e
ON c.CompID = e.CompID
WHERE c.CompID = 12986

but if run what you have, no records are returned at all even though there is one record in tblAwards that corresponds in tblExecutives for each executive. Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-07-20 : 12:18:27

We'll need to see some sample data then of each table and what the expected result set is using that sample data. The sample data should reflect your current problem.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources