Re: query help request [2x] - Mailing list pgsql-novice

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