NYCPHP Meetup

[Carlos A Hoyos]

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