Re: Join several tables (to fetch user info), but one of them is optional (user avatar) - Mailing list pgsql-general

On 12/19/2012 5:54 AM, Alexander Farber wrote:
> I wonder though what is the syntax if I wanted
> the other 2 tables (drupal_field_data_field_gender
> and drupal_field_data_field_city) to be joined
> as a "left outer join" as well?

you were using an implied join instead of an explicit one.

     select
         u.uid,
         /* u.pass, */
         f.filename as avatar,
         (g.field_gender_value='Female') as female,
         c.field_city_value as city
    from
           drupal_users u
             JOIN drupal_field_data_field_gender g ON g.entity_id=u.uid
             JOIN drupal_field_data_field_city c ON c.entity_id=u.uid
        LEFT JOIN drupal_file_managed f ON u.picture=f.fid
         where
           u.name='mvp';


is more like how you code a conventional JOIN.