help with complex query, any gurus or geniuses around?

[william67]

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

[code green]

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

/*it must find only certain values from y_id, lets say 2 is ok but 4 and 7 are not.*/
SELECT x_id y_id FROM graph
WHERE y_id IN (2) AND y_id IN 
/*the query must then return all y_id's of the x_id's it found in the first query but ignoring the 9*/
(SELECT y_id FROM graph WHERE y_id IN 
(SELECT y_id FROM graph WHERE y_id NOT IN (9)
AND y_id IN (SELECT y_id FROM grapg WHERE x_id = 9))

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

[william67]

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

much appreciated

[william67]

code green

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

very much appreciated!