Hello,
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).
Regards
Alex
