NYCPHP Meetup

[Kenneth Dombrowski]

For multiple emails, I think you want to explicitly use LEFT JOINS, to
allow some NULLs in case a particular user only has one email attribute

        SELECT 
                user.user_id, 
                at0.attribute_name as name , 
                at1.attribute_name as email1 , 
                at2.attribute_name as email2 , 
                ...
        FROM 
                Users as user
        -- with INNER JOIN users will not be returned unless they have
        -- a first_name attribute
        INNER JOIN User_Attributes as at0 ON ( 
                user.user_id = at0.user_id 
                AND at0.attribute_name = 'first_name' 
        )
        -- with LEFT JOIN, NULL will be returned if the column doesn't
        -- exist
        LEFT JOIN User_Attributes as at1 ON (
                user.user_id = at1.user_id 
                AND at1.attribute_name = 'email' 
        )
        LEFT JOIN User_Attributes as at2 ON (
                user.user_id = at2.user_id 
                AND at2.attribute_name = 'email' 
                AND at1.attribute_id != at2.attribute_id 
        )
        WHERE 
                -- whatever
        
But the "at1.attribute_id != at2.attribute_id" thing is extremely ugly,
and moreso if you begin supporting 3, 4, + email attributes

What you really need, if you want to support an arbitrary number of
email addresses, is a cursor... of course, you need a recent MySql for
that



On 06-03-02 00:12 -0500, Peter Sawczynec wrote:
> 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
>