Finding specific records based on user's preferences

Given a user, select only the projects that he is interested in.

So a user table has x, y, z columns that have a value of 1 (if interested) and 0 (if not interested)

Once we get the user we need to get all the projects that have the atleast one of x, y, z value similar.

So given:

prj title     |   x  |   y   |   z   |
__________________________________________
prj1         |   1   |   0   |   1   |

prj2         |   1   |   1   |   0   |

prj3         |   0   |   0   |   1   |

and the user table:

user id      |   x   |   y   |   z   |
__________________________________________

user1        |   1   |   0   |   0   |

user2        |   1   |   1   |   0   |

user3        |   0   |   0   |   1   |

Need to find a query that will give me a list of projects that a given user (user1) is interested in.

Result should be (if user 1 is selected): prj1 and prj2 Result should be (if user 3 is selected): prj1 and prj3

any ideas on how this can be achieved? I开发者_Go百科 am not sure where to start from.

I am not sure if this can be done in just one simple query?

There's one solution with just one query

select p.title
from prj as p left join user as u on ( ( 
    (u.x = p.x) && (u.x=1) ) || ( (u.y = p.y) && (u.y=1....

and so on, well I hope you got the idea.

Try this:

SELECT p.prj_title
FROM prj AS p
JOIN users AS s ON (
    (u.x=p.x AND u.x=1)
    OR (u.y=p.y AND u.y=1)
    OR (u.z=p.z AND u.z=1)
)
WHERE u.user_id='user1';