Thread: Two-way query

Two-way query

From
Vernon Wu
Date:
I need help to find an effective method to resolve a two-way query problem. 

Problem:
One is looking for friends who meet his criteria while people will not show up in his friend list if this person's
criteriadon't 
 
meet their criteria.

His criteria:
age: 24 ? 32
education: at least with a bachelor degree
occupation: programmer

DB tables:

profile:    id | age | education | occupation

preference:    id | age range | education set | occupation set

Search:

Batch of sql statements:
1.fetch this person's preference (select criteria)
2.Use his preference issue a query statement as:
select id from profile where (age < 33 and age > 23) and (education='bachelor' or education='master' or 
education='phd') and (occupation='programmer')
3.fetch preference with ids from the previous query.
4.Issue a similar query of the step 2 to find out whether he is qualified to be a friend of people in step 2

What is the most effective sql statement for this problem?


Thank in advance for your advice.

Vernon