NYCPHP Meetup

[Mark Armendariz]

I've a 2 part answer...

First, your problem.

> On Behalf Of Peter Sawczynec
> ...
> 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


> On Behalf Of Carlos A Hoyos
> ...
> 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,

He's correct, except you'll want more control within the joins than Carlos'
example.

Here's a way to do it (worked correctly on my local mysql 4.023)

SELECT
    user.user_id,
    email.attribute_value as user_email,
    name.attribute_value as user_name
FROM
    users user
LEFT JOIN
    user_attributes active ON
    user.user_id = active.user_id 
    AND active.attribute_name = 'active' 
LEFT JOIN
    user_attributes name ON
    user.user_id = name.user_id 
    AND name.attribute_name = 'name' 
LEFT JOIN
    user_attributes email ON
    user.user_id = email.user_id 
    AND email.attribute_name = 'email' 
WHERE active.attribute_value = 'yes'

> 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

95 79 email another at sal.com  // and another email for good ol' sal for good
measure

produces this (sorry if spacing's off):

user_id   	 user_email   	 user_name
78 	joe at joe.com 	joe
79 	sal at sal.com 	sal
79 	another at sal.com 	sal

> On Behalf Of Kenneth Dombrowski
> ...
> But the "at1.attribute_id != at2.attribute_id" thing is 
> extremely ugly, and moreso if you begin supporting 3, 4, + 
> email attributes

Close, but there's no need for a separate join for each email.

Essentially, you're getting a list of users from the users table, and then
attaching attribute values pertaining to that user per attribute.  The joins
hold their constraints on the data they need and the only 'where' condition
you need is to make sure the user is active.

This works well if only ONE of the attributes has multiple values (email in
this case).  If they would also have multiple phone numbers your results
might get messy (at least for a single query, for that you'd want different
queries per joined list).

> On Behalf Of Anirudhsinh Zala
> ...
> => Probably best way is to merge both tables "User" and 
> "User_attributes" as there is no any good advantage of this 
> kind of normalization.

I'm GUESSING that the reason you have the tables set up this way is for a
dynamic survey system of sorts, in which you'll want someone to be allowed
to add / remove fields as they wish without messing with the data structure.
So removing the normalization probably won't work for you in this respect.
If it's something else, you might want be sure you've good reason to make a
database engine on top of a damned fine database engine.

But, the part of Anirudhsinh's argument that I agree with is that the users
table has no purpose.  Now, if you're adding fields to it (username and
password for instance), and have your reasons for meta columns that don't
involve recreations and wheels then forget this part of the problem
entirely.  BUT, if it ONLY holds a user_id, consider getting rid of it.  You
could use this query instead (tested correctly as well): 

SELECT
    active.user_id,
    email.attribute_value as user_email,
    name.attribute_value as user_name
FROM
    user_attributes active
LEFT JOIN
    user_attributes name ON
    active.user_id = name.user_id 
    AND name.attribute_name = 'name' 
LEFT JOIN
    user_attributes email ON
    active.user_id = email.user_id 
    AND email.attribute_name = 'email' 
WHERE
    active.attribute_value = 'yes'



Good luck!!!

Mark Armendariz
http://www.enobrev.com/