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