Page 1 of 1

SQL query to display users matching 2 criteria

PostPosted: 10:21pm, Wed 24 Mar, 2010
by 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

Re: SQL query needed please

PostPosted: 12:03am, Fri 26 Mar, 2010
by 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