MySQL Select Statement Help

[robdiablo]

Hi and thanks for letting me post my question here. I'm trying to create a select statement based on the following conditions, but first, let me visualize the tables. These have been created to give you an idea of the content of the tables but are not the actual values, etc...

T1:
----------------------------------------------
| UID | username    | type                |
----------------------------------------------
|  1    | skipnsus      | couple             |
----------------------------------------------
|  2    | buddy          | single male     |
----------------------------------------------
|  3    | lisak             | single female  |
----------------------------------------------
|  4    | robnjen        | couple            |
----------------------------------------------

T2:
------------------------------------------------------
| UID | firstName     | gender            | age |
------------------------------------------------------
|  1    | Skip             | male                | 23   |
------------------------------------------------------
|  1    | Susan          | female             | 20   |
------------------------------------------------------
|  2    | Buddy          | male                | 23   |
------------------------------------------------------
|  3    | Lisa              | female             | 18   |
------------------------------------------------------
|  4    | Rob              | male                | 23   |
------------------------------------------------------
|  4    | Jen               | female             | 23   |
------------------------------------------------------

As you can see, T1 contains a user database where users can be single male, single female or couple.

T2 is connected to T1 by UID and contains details about the user and if its a couple, a separate row for each person in the couple.

Now let's say I want to get the username of all users where females are 20 and males are 23. According to these tables, the results should return usernames: skipnsus and buddy. But it shouldn't return robnjen or lisak because both conditions do not apply.

Here is the select statement I've come up with:

SELECT t1.userName FROM t1, t2 WHERE t1.UID = t2.UID AND ((t2.age = '23') AND t2.gender = 'male' AND (t1.type= 'single male' OR t1.type = 'couple')) AND ((t2.age = '20') AND t2.gender = 'female' AND (t1.type = 'single female' OR t1.type = 'couple')

Now obviously, this statement doesn't work because I'm trying to select from two different rows in T2 and it cancels each other out to give no results.

What would be the correct way to write the SQL statement? I've been at this for 3 days straight now trying all kind of combinations of joins, etc... but I just can't figure it out. There has to be a way right?

[stevelg]

Challenging!
You cannot do what you want the way you are trying to do it. For singles it is easy using;

where ti.UID=t2.UID and((t2.gender='male' and t2.age='23) or (t2.gender='female' and t2.age='20'))

but when it comes to couples you need to test two records in table two, where you only access one record at a time!
The best way I can see to do it is to turn the problem around to reverse the logic but I am not sure how (or if) this can be done with MySQL.
You need a structure like;

WHERE t1.UID=t2.UID AND (t2.UID !in (SELECT t2.UID from t1, t2 WHERE (t1.UID=t2.UID) AND ((t1.type='single male' AND t2.age!='23') OR (t1.type='single female' AND t2.age!='20') OR (t1.type='couple' AND (t2.gender='male' AND t2.age!='23') OR (t2.gender='female' AND t2.age!='20')))))

[vankysrox]

plz try to join tables using "view" in sqlserver2005 u can njoy it.... n can solve your probs