help with complex query, any gurus or geniuses around?

I'm having a hard time building a query to do what I need to do and was hoping some genius could help me out, I need to do a complex query and any and all help is much appreciated

this is the table, with sample data

------------------
| x_id | y_id |
------------------
| 3 | 9 |
------------------
| 1 | 2 |
------------------
| 7 | 1 |
------------------
| 1 | 4 |
------------------
| 3 | 7 |
------------------
| 4 | 9 |
------------------
| 1 | 9 |
------------------


The query must find a value, let's say 9 in column y_id
it should return
------------------
| 3 | 9 |
------------------
| 4 | 9 |
------------------
| 1 | 9 |
------------------
however there is a catch, the query must then return all y_id's of the x_id's it found in the first query but ignoring the 9, it would return
------------------
| 1 | 2 |
------------------
| 1 | 4 |
------------------
| 3 | 7 |
------------------
because the x_ids of 1 and 3 were found earlier

then to make it even more complicated, it must find only certain values from y_id, lets say 2 is ok but 4 and 7 are not. so in the end we have
------------------
| 1 | 2 |
------------------

if you understand what I posted, can you provide the query code?
I'm completely lost!

much appreciated and thanks for your time

re: help with complex query, any gurus or geniuses around?

The simpler way to go seems to be nested sub-queries.
Is it just the final result you are after?

Looks to me like you are using algebra to read a graph.
As with some algebraic problems you need to work backwards then forwards

Try something like Don't expect this to work first time.
Test the sub queries seperatately then two together then three together.
Hope I have at least helped you on your way

re: help with complex query, any gurus or geniuses around?

thank you very much for the reply and your time
I will investigate your solution

much appreciated

re: help with complex query, any gurus or geniuses around?

code green

I wanted to thank you for your reply and example
it worked exactly as I needed

very much appreciated!