WHERE clause for one to many relationship

[#1]

Hi,

I have a bit of a problem with an import script using some interesting WHERE clauses. Any help in getting out of this one would be appreciated.

I have two tables, CALLS and KEYWORDS which share the common field Call_No. The KEYWORD table has an additional field called Keyword. The KEYWORD table contains both primary and secondary keywords with no distinction between them exept a # at the start of the primary keywords. I am looking for Call_No with a primary Keyword #BNMS and then need to return all Keyword for that Call_No.

For Example

CALLS

Call_No
12345
23456
34567

KEYWORDS

Call_No	Keyword
12345	#BNMS
12345	Other
23456	#BNMS
23456	Hosting
34567	Support

I would hope to get something like this

CALLS
12345
23456

KEYWORDS
#BNMS
Other
Hosting

Thanks in advance,

Adam

[#2]

I am not sure the logic for deleting the below row:

34567

Support

With that exception attached an example to demonstrate a solution to your problem. Hope this helps you

Kiran.

[#3]

Hi Kiran,

Thanks for the quick response. Call_No 34567 is excluded as it does not have the keyword #BNMS related to it. Therefore I don't need to import the call or any of it's other associated keywords.

I'll give your solution a look and see if I can make it fly.

Thanks,

Adam