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

Transact-SQL (2000)

Beginners SQL Help

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2006-04-28 : 13:46:24

I have 3 tables named TCompanies, TDirectorships, TDirectors like below in a SQL Server 2000 view named v_SearchResults. Each company and director usually has many directorships.

TCompanies
CompID (PK)
Industry
CompanyName

TDirectorships
CompID (FK)
IDDirship (PK)
IDDir (FK)
Salary

TDirectors
IDDir (PK)
Name
Age
Gender

I have the tables joined like this:

TDirectorships INNER JOIN TDirectors ON TDirectorships.IDDir = TDirectors.IDDir INNER JOIN
TCompanies ON TDirectorships.CompID = TCompanies.CompID

Say I want to do a search like this from a aspx page

SELECT * v_SearchResults WHERE Industry='Software'

which produces a new record for every company based on the number of directorships there are like this

IBM Software
IBM Software
IBM Software
IBM Software
IBM Software
Adobe Software
Adobe Software

when I just want one record per company.

IBM Software
Adobe Software

Is this possible based on what I have here. Unfortunately I have to use SELECT * in this case? Thanks.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-04-28 : 14:39:46

This might be way to obvious but why not just query against the TCompanies table:
select CompanyName from tcompanies where industry = 'Software'

instead of hitting a view that joins all 3 tables?

It would be cleaner and perform better. But if you really have to hit the view for a thrill or class project, you could issue a SELECT DISTINCT CompanyName from v_SearchResults where industry = 'Software'.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

evanburen
Posting Yak Master

167 Posts

Posted - 2006-04-28 : 15:00:29

The user is able to generate dynamic queries by selecting a wide range of fields from all 3 tables. My goal is to produce one record per company in my web page no matter how many director or directorships records are associated with that company. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-02 : 02:15:57

Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources