Re: Join several tables (to fetch user info), but one of them is optional (user avatar)

Alexander Farber wrote:
> given a user name in a Drupal 7.17 database
> using PostgreSQL 8.4.13 I am trying to fetch user info
> (uid, city, gender, avatar) distributed over several tables.
>
> The avatar is however optional - some users don't have it.
>
> For users, that do have avatars my query works well:
>
> # select
> u.uid,
> /* u.pass, */
> f.filename,
> g.field_gender_value,
> c.field_city_value
> from
> drupal_users u,
> drupal_file_managed f,
> drupal_field_data_field_gender g,
> drupal_field_data_field_city c
> where
> u.name='Alex' and
> u.picture=f.fid and
> g.entity_id=u.uid and
> c.entity_id=u.uid
> ;
> uid | filename | field_gender_value | field_city_value
> -----+--------------------------+--------------------+------------------
> 1 | picture-1-1312223092.jpg | Male | Bochum
> (1 row)
>
> However for users, who don't have avatar I get empty result.
>
> When I omit the drupal_file_managed table - it works again:
>
> # select
> u.uid,
> /* u.pass, */
>
> g.field_gender_value,
> c.field_city_value
> from
> drupal_users u,
>
> drupal_field_data_field_gender g,
> drupal_field_data_field_city c
> where
> u.name='mvp' and
>
> g.entity_id=u.uid and
> c.entity_id=u.uid
> ;
> uid | field_gender_value | field_city_value
> -------+--------------------+------------------
> 18539 | Male | Moscow
> (1 row)
>
> How could I modify my join statement to
> ensure that it always returns 1 row for valid users -
> regardless if they have avatar or not?
>
> Do I want a "left outer join" here?
> (I'm afraid it will return several rows instead of 1).

Yes, you need an outer join for that.

Only use an outer join to add the "drupal_file_managed" table,
the other tables should be joined with an inner join.

Yours,
Laurenz Albe