query help request - Mailing list pgsql-novice

From Andrei Ivanov
Subject query help request
Date
Msg-id Pine.LNX.4.50L0.0303312139150.2309-100000@webdev.ines.ro
Whole thread Raw
List pgsql-novice
Hello,
  I have 3 tables:

  users: id serial primary key, nick varchar and other stuff
  profiles: user_id which is a foreign key pointing to users(id),
            other things about the user (a user doesn't have to own a
            profile, but in this listing I want only users with a profile)
  pictures: id serial pk, user_id foreign key, picture varchar (a user
            doesn't have to own a picture or more)

What I want to do is to list all the users, something like this:

id, nick, stuff from profile, has_picture

has_picture is a flag which indicates if the user has a picture or not.

First time I did something like this:

SELECT u.id, u.nick, pr.keywords, COALESCE(p.id, 0) AS has_picture
FROM users u JOIN profiles pr ON u.id = pr.user_id
LEFT JOIN pictures p ON u.user_id = p.user_id;

but if a user has more than 1 picture, it is listed more then once.

Then I came up with this:

SELECT u.id, u.nick, pr.keywords,
  COALESCE((SELECT id FROM pictures WHERE user_id = u.id), 0) AS has_picture
FROM users u JOIN profiles pr ON u.id = pr.user_id;

but, if I understand it correctly, for each user, it will issue a query on
the pictures table to find a picture for that user, which I think it's an
overkill.

How can I do this right ? (I hope I didn't embarrassed myself by
overlooking an obvious answer.)

Thanks


pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: Alias for data types?
Next
From: Oliver Elphick
Date:
Subject: Re: Postgres logs w/ Logrotate