Re: query help request [2x]

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(dot)ivanov(at)ines(dot)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
>