[nycphp-talk] A SQL Query Conundrum. I Need Your Assistance...

Halter, Shari SHalter at
Thu Mar 2 08:54:36 EST 2006

Speaking of MySQL questions, can anyone recommend a good list for MySQL? 

Shari Halter

We've Moved!

Our new address is 51 Madison Avenue, New York, NY 10010.
Our telephone and fax numbers remain the same.

Shari L. Halter
Web Programmer
Thornton Tomasetti
51 Madison Avenue
New York, NY  10010
T 917.661.7800  F 917.661.7801  
D 917.661.7970  
SHalter at

-----Original Message-----
From: Peter Sawczynec [mailto:ps at] 
Sent: Wednesday, March 01, 2006 10:30 PM
To: 'Org, Talk at Nyphp.'
Subject: [nycphp-talk] A SQL Query Conundrum. I Need Your Assistance...

Sorry, another MySQL question on the PHP list, but there are so many great
users here...

I need to get all the active users email and name for personalization.

I need the correct SELECT query addressing two tables that have a one to
many relationship linked by "user_id" like the relationship shown below:

Table: Users
Fields: id   user_id 

Table: User_Attributes
Fields: id   user_id   attribute_name   attribute_value

So in User_Attributes table you see data like so:
89 78 email joe at
90 78 name joe
91 78 active yes
92 78 title CEO
93 79 email sal at
94 79 name sal

I need to get the email and name of all the users where:
attribute_name "active"  = attribute_value "yes"

I'm using this query so far:

SELECT a.user_id, b.attribute_name, b.attribute_value FROM users AS a LEFT
JOIN User_Attributes AS b ON a.user_id = b.user_id WHERE b.attribute_name IN
('active', 'email', 'salutation', 'first_name', 'last_name') ORDER BY

...but this returns a rowset where each user has four rows with an attribute
value in each row.

I need all four attributes from each user to be returned in one neat row.

Additionally, I could use all the non-active users filtered out by the SQL.

Any help here?

Warmest regards,

Peter Sawczynec,
Technology Director
_Design & Interface
_Database Management
ps at

New York PHP Community Talk Mailing List
New York PHP Conference and Expo 2006
Show Your Participation in New York PHP

The information in this email and any attachments may contain
confidential information that is intended solely for the
attention and use of the named addressee(s).  This message or
any part thereof must not be disclosed, copied, distributed or
retained by any person without authorization from the addressee.
If you are not the intended addressee, please notify the sender
immediately, and delete this message. 

More information about the talk mailing list