Обсуждение: query help request [2x]

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

Andrei Ivanov <andrei.ivanov@ines.ro> writes:
> 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;

This will actually fail if any user has more than one picture.

I think you have to go with

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

This should perform reasonably well as long as there's an index on
pictures.user_id.

            regards, tom lane

Thanks.
Anyway, my query works, I've tested it.. I just didn't like its lack of
elegance... :)

On Tue, 1 Apr 2003, Tom Lane wrote:

> Andrei Ivanov <andrei.ivanov@ines.ro> writes:
> > 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;
>
> This will actually fail if any user has more than one picture.
>
> I think you have to go with
>
> SELECT u.id, u.nick, pr.keywords,
>   EXISTS(SELECT 1 FROM pictures WHERE user_id = u.id) AS has_picture
> FROM users u JOIN profiles pr ON u.id = pr.user_id;
>
> This should perform reasonably well as long as there's an index on
> pictures.user_id.
>
>             regards, tom lane
>