NYCPHP Meetup

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

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


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

Thanks,
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 ThorntonTomasetti.com



-----Original Message-----
From: Peter Sawczynec [mailto:ps at pswebcode.com] 
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 joe.com
90 78 name joe
91 78 active yes
92 78 title CEO
93 79 email sal at sal.com
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
a.user_id 


...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
PSWebcode
_Design & Interface
_Ecommerce
_Database Management
ps at pswebcode.com
718.796.1951
www.pswebcode.com

_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
New York PHP Conference and Expo 2006
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.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