NYCPHP Meetup

[Peter Sawczynec]

Just for reference, this amalgamated query, does it all:

SELECT user.user_id, at1.attribute_value AS name, at2.attribute_value AS
email 
FROM Users AS user, User_Attributes AS at1, User_Attributes AS at2 
WHERE user.user_id = at1.user_id 
AND user.user_id = at2.user_id 
AND at1.attribute_name = 'first_name' 
AND at2.attribute_name = 'email'
AND user.user_id
IN
(SELECT user_id 
FROM User_Attributes AS at3
WHERE at3.attribute_name = 'active' AND at3.attribute_value = 'yes' )

Peter

-----Original Message-----
From: talk-bounces at lists.nyphp.org [mailto:talk-bounces at lists.nyphp.org] On
Behalf Of Carlos A Hoyos
Sent: Wednesday, March 01, 2006 11:06 PM
To: NYPHP Talk
Subject: Re: [nycphp-talk] A SQL Query Conundrum. I Need Your Assistance...



Two very simple options:
1- You can join with the same table multiple times just by giving it
different alias. So for example this following query will get all users id,

SELECT user.user_id, at1.attribute_value as name, at2.attribute_value as
email, FROM users AS user, User_Attributes AS at1,  User_Attributes AS at2
WHERE user.user_id = at1.user_id AND user.user_id = at2.user_id AND
at1.attribute_name = 'last_name' AND at2.attribute_name = 'email'

you get the idea...

2- You can use subqueries, for example to get emails for all active users:
select at1.attribute_value from User_Attributes AS at1 where
at1.attribute_name = 'email' and at1.user_id in
      select (user_id from User_Attributes AS at2 where  at2.attribute_name
= 'active' and at2.attribute_value = 'yes')

you get the point...

I know it's none of my business, but maybe you should use a query like that
one to de-normalize the table, I can't think of a good reason for such level
of normalization.



Carlos Hoyos, Tools Architect
Global Production Services - Tools, ibm.com
1133 Westchester Ave, # 2e 524, White Plains, NY 10604
Phone: 914.642.3569 TieLine: 224.3569
cahoyos at us.ibm.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