From: | Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | query help request |
Date: | 2003-03-31 18:54:18 |
Message-ID: | Pine.LNX.4.50L0.0303312139150.2309-100000@webdev.ines.ro |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2003-03-31 21:24:48 | Re: Postgres logs w/ Logrotate |
Previous Message | Tom Lane | 2003-03-31 18:13:31 | Re: Alias for data types? |