|
|
Posted by usenet on 06/12/06 17:15
I wonder if anyone here has accomplished this task with PHPlist, or if it's
clear enough for the regular MySQL gurus to offer advice. I'm omitting the
table structures and such for brevity but will amend if I'm making too much of
an imposition.
In English, I need MySQL to return:
(1) All attribute values for (2) users who are members of list ID 2, and (3)
which contain the value 'th' in attributes 1, 2, or 3.
Individually, those criteria are simple enough to extract, but I'm having
trouble putting them together into a single MySQL (v 4.1.19) statement.
Working backward :
(3) (fname is attribute 1, lname 2, company 3)
mysql> SELECT * FROM phplist_user_user_attribute WHERE
phplist_user_user_attribute.attributeid = '1' AND
phplist_user_user_attribute.value LIKE '%th%' OR
phplist_user_user_attribute.attributeid = '2' AND
phplist_user_user_attribute.value LIKE '%th%' OR
phplist_user_user_attribute.attributeid = '3' AND
phplist_user_user_attribute.value LIKE '%th%'
GROUP BY userid;
(2)
mysql> SELECT * from phplist_listuser WHERE phplist_listuser.listid = '2' ORDER
BY userid;
Putting them together, I get the users who match both queries :
mysql> SELECT * FROM
(SELECT * FROM phplist_user_user_attribute WHERE
phplist_user_user_attribute.attributeid = '1' AND
phplist_user_user_attribute.value LIKE '%th%' OR
phplist_user_user_attribute.attributeid = '2' AND
phplist_user_user_attribute.value LIKE '%th%' OR
phplist_user_user_attribute.attributeid = '3' AND
phplist_user_user_attribute.value LIKE '%th%')
AS searchtermhits LEFT JOIN
(SELECT * from phplist_listuser WHERE phplist_listuser.listid = '2')
AS memberhits
ON searchtermhits.userid = memberhits.userid
ORDER BY searchtermhits.userid;
+-------------+--------+---------------------------------+--------+--------+
| attributeid | userid | value | userid | listid |
+-------------+--------+---------------------------------+--------+--------+
| 3 | 56 | Arthur & Associates, L.L.C. | 56 | 2 |
| 3 | 57 | Arthur & Associates, L.L.C. | 57 | 2 |
| 2 | 70 | Smith | NULL | NULL |
| 1 | 75 | Timothy | NULL | NULL |
| 3 | 78 | Drither & Bath LLP | 78 | 2 |
| 3 | 79 | Drither & Bath LLP | 79 | 2 |
| 1 | 83 | Keith | NULL | NULL |
| 3 | 84 | The Toms School | NULL | NULL |
| 2 | 86 | Thren | NULL | NULL |
| 1 | 86 | Kathy | NULL | NULL |
| 2 | 125 | Thomson | NULL | NULL |
| 2 | 192 | Thayer | NULL | NULL |
| 1 | 199 | Kenneth | NULL | NULL |
| 1 | 213 | Timothy | 213 | 2 |
| 3 | 220 | The Toms School | NULL | NULL |
| 3 | 223 | The VIN Company | NULL | NULL |
| 2 | 224 | Thomson | 224 | 2 |
| 3 | 226 | Jonhson Brothers | NULL | NULL |
| 1 | 227 | Timothy | NULL | NULL |
| 3 | 251 | Drither & Bath LLP | 251 | 2 |
| 1 | 255 | Timothy | 255 | 2 |
| 3 | 265 | Johnson Brothers | NULL | NULL |
| 2 | 271 | Smith | 271 | 2 |
| 3 | 271 | Smith & Assoc., LLC | 271 | 2 |
| 3 | 282 | Smithtown LLC | NULL | NULL |
| 3 | 284 | Black Brothers & Co. | NULL | NULL |
| 3 | 300 | Johnson Brothers | NULL | NULL |
| 2 | 306 | Thayer | NULL | NULL |
+-------------+--------+---------------------------------+--------+--------+
28 rows in set (0.01 sec)
(1) Now here's were I get stuck. In column 2 I have the list of
phplist_user_user_attribute.userid that meet all the criteria, with expected
dupes. How to take that list and return _all_ attributes (1-12) for each userid
in this SELECT ? I can't figure out the correct syntax for the next JOIN. I've
tried everything -- except the one that works, of course. :-)
Any and all help will be greatly appreciated.
[Back to original message]
|