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