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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you, I've ended up with:

# 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 LEFT OUTER JOIN drupal_file_managed f on
(u.picture=f.fid),
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 | avatar | female | city
-------+--------+--------+--------------
18539 | | f | Moscow
(1 row)

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?

Regards
Alex

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general