Join on one to many relationship

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 on one to many relationship

Author

Topic

kkroese
Starting Member

2 Posts

Posted - 2007-05-29 : 04:14:46

Hi

I have a table called tCandidates with a pkiCandidateID
and another table with tKeywords. There is a one to many relationship between candidates and keywords (1 candidate have many keywords)

As part of a much larger query I need to join the keyword table to the candidate table and only return on result. ( I need to use 'and' )

Any help with this?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-29 : 04:19:20

u mean the join should give only one record isn't it?

do u have any criteria or any random row.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-29 : 04:24:06

Please post your table DDL, sample data and the result that you need.

kkroese
Starting Member

2 Posts

Posted - 2007-05-29 : 04:43:45

Hi Guys

CREATE TABLE [dbo].[tCandidates] (
[pkiCandidateID] [int] IDENTITY (1, 1) NOT NULL ,
[sName] [varchar]

) ON [PRIMARY]
GO

INSERT INTO tCandidates (sName,) Values ('John')
INSERT INTO tCandidates (sName,) Values ('Mary')
INSERT INTO tCandidates (sName,) Values ('James')
INSERT INTO tCandidates (sName,) Values ('Kevin')

==================================================
CREATE TABLE [dbo].[tCandidateKeywordEntry] (
[pkiCandidateKeywordEntryID] [int] IDENTITY (1, 1) NOT NULL ,
[fkiCandidateID] [int] NOT NULL ,
[fkiCandidateTypeKeywordEntryID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tCandidateKeywordEntry (fkiCandidateID, fkiCandidateTypeKeywordEntryID) VALUES (1, 1)

INSERT INTO tCandidateKeywordEntry (fkiCandidateID, fkiCandidateTypeKeywordEntryID) VALUES (1, 2)

INSERT INTO tCandidateKeywordEntry (fkiCandidateID, fkiCandidateTypeKeywordEntryID) VALUES (1, 3)

INSERT INTO tCandidateKeywordEntry (fkiCandidateID, fkiCandidateTypeKeywordEntryID) VALUES (2, 1)

In above example I need to return one row per candidateID where candidates have keywordentry id of (1 and 2)

This needs to be an innerJoin on candidates.
This is part of a much larger query that is used for a search function..

Any helpw would be much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources