MS SQL Query Help

[johny]

I have a table as the following:

Key| Related Key| Other field …..
001| #003,#005|
002|
003| #001|
004|
005|
There is only one number in Field Key(001),but there are more than one number with # (#003,#005) in Field Related Key and speareted by ",".

I want to create a query:
SELECT * FROM table WHERE Key is one of Related Key(exclude #, only number).

How do I create this query?

Thanks!

[MAK]

How the result is suppose to look like?

give example

[johny]

Sorry, I did not ask a right question.
Here is table
Key| Related Key| Release|...
001| #003,#005 |
002| #003 |
003| #001,#002 | R |
004|
005|

1) I created a query that I only select the Release field is "R". I know how to create this one.
I should got one record from this query:
Key| Related Key| Release|...
003| #001,#002| R |

2) Then I try to create a query that only show two records with key 001 and 002
Key| Related Key| Release|...
001| #003,#005 |
002| #003 |

Originally posted by MAK
How the result is suppose to look like?

give example

[MAK]

create table keys (keys varchar(10), relatedkeys varchar(100), release char(1))
insert into keys select '001','#003,#005',''
insert into keys select '002','#003',''
insert into keys select '003','#001,#002','R'
insert into keys select '004','',''
insert into keys select '005','#003,#001',''



--select * from keys where release ='R'

--If release is only in one row you can use the below query

declare @x varchar(100)
declare @query varchar(1000)
set @x = (select replace(replace(relatedkeys,'#',''),',','","') as relatedkeys from keys where release ='R')
set @query ='select * from keys where keys in ("' + @x + '")'
--print @query
exec (@query)