SQL query to display users matching 2 criteria

[ph2010]

Hello, I need a SQL query to display or export all email addresses which match two or more criterias.

Values of different types of criteria (radio, list, text) are stored in "phplist_user_user_attribute" .
If the criteria is of type 'select from a list' it only stores the value as an ID and the real name of each ID is stored for example in "phplist_listattr_countries" (value 19 in phplist_user_user_attribute)
If I add a criteria of type text, it stores the value as it is (some_text)
Lets call second attribute Hobby with attributeid = 2 (see phplist_user_attribute)

Now how do I find all email address which match Country = England (19) AND Hobby = some_text ?

table: phplist_user_user_attribute

attributeid___userid___value
1___________1_______19
2___________1_______some_text

[szucsati]

Code: Select all

SELECT
 email,
 t1.userid,
 t1.value AS Country,
 t2.value AS Hobby
FROM phplist_user_user_attribute t1,
 phplist_user_user_attribute t2,
 phplist_user_user
WHERE t1.userid = t2.userid
 AND t1.attributeid = 1
 AND t1.value = 3
 AND t2.attributeid = 2
 AND t2.value = "some_text"
 AND t1.userid = phplist_user_user.id;

This will give something like:

Code: Select all

email    userid    Country    Hobby
user00005@domain.com    5    3    some_text