SQL Question
I have two queries I would like to combine into one.
I have a table that represents a user's contacts. It has fields like "id,
owner_id, user_id". Owner ID cannot be null but user_id can be null. They
are numeric field, the ID is just generated.
I want a query to retrieve all of a user's contacts but add in a field to
know if there is a mutual relationship between the contact owner.
I get all of a user's contacts like this:
SELECT c.* FROM contacts c WHERE c.owner_id = :id;
I can then get all contacts that have the owner as a user like this:
SELECT c.* FROM contacts c WHERE EXISTS(
SELECT 1 FROM contacts c2 WHERE c2.user_id = c.owner_id AND
c2.owner_id = c.user_id)
AND c.owner_id = 1;
But what I'd like is to have the EXISTS clause of the second query to show
up as a BOOLEAN field in the result set. I don't want it to scope the
results, just tell me for each contact of the owner, do they also have her
as a contact?
I tried this but it didn't work:
SELECT c.*, EXISTS(SELECT 1 FROM contacts c2 WHERE c2.owner_id =
c1.user_id AND c2.user_id = c1.owner_id)
WHERE c.owner_id = :owner;
Thanks!
Heh, scratch that, the EXISTS query DOES work. Is this the most efficient
way to perform this kind of query? Thanks!
On Tue, Apr 1, 2014 at 1:21 PM, Robert DiFalco <robert.difalco@gmail.com>wrote:
Show quoted text
I have two queries I would like to combine into one.
I have a table that represents a user's contacts. It has fields like "id,
owner_id, user_id". Owner ID cannot be null but user_id can be null. They
are numeric field, the ID is just generated.I want a query to retrieve all of a user's contacts but add in a field to
know if there is a mutual relationship between the contact owner.I get all of a user's contacts like this:
SELECT c.* FROM contacts c WHERE c.owner_id = :id;
I can then get all contacts that have the owner as a user like this:
SELECT c.* FROM contacts c WHERE EXISTS(
SELECT 1 FROM contacts c2 WHERE c2.user_id = c.owner_id AND
c2.owner_id = c.user_id)
AND c.owner_id = 1;But what I'd like is to have the EXISTS clause of the second query to show
up as a BOOLEAN field in the result set. I don't want it to scope the
results, just tell me for each contact of the owner, do they also have her
as a contact?I tried this but it didn't work:
SELECT c.*, EXISTS(SELECT 1 FROM contacts c2 WHERE c2.owner_id =
c1.user_id AND c2.user_id = c1.owner_id)
WHERE c.owner_id = :owner;Thanks!
Is this the most efficient way to perform this kind of query?
I don't think there is one answer that's always correct, but you could
compare it with a LEFT OUTER JOIN.
There are lots of articles and blog posts about EXISTS vs OUTER JOIN
vs IN, for all the major RDBMSes. Note that not all these options give
identical results.
Paul
On Tue, Apr 1, 2014 at 1:27 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:
Heh, scratch that, the EXISTS query DOES work. Is this the most efficient
way to perform this kind of query? Thanks!On Tue, Apr 1, 2014 at 1:21 PM, Robert DiFalco <robert.difalco@gmail.com>
wrote:I have two queries I would like to combine into one.
I have a table that represents a user's contacts. It has fields like "id,
owner_id, user_id". Owner ID cannot be null but user_id can be null. They
are numeric field, the ID is just generated.I want a query to retrieve all of a user's contacts but add in a field to
know if there is a mutual relationship between the contact owner.I get all of a user's contacts like this:
SELECT c.* FROM contacts c WHERE c.owner_id = :id;
I can then get all contacts that have the owner as a user like this:
SELECT c.* FROM contacts c WHERE EXISTS(
SELECT 1 FROM contacts c2 WHERE c2.user_id = c.owner_id AND
c2.owner_id = c.user_id)
AND c.owner_id = 1;But what I'd like is to have the EXISTS clause of the second query to show
up as a BOOLEAN field in the result set. I don't want it to scope the
results, just tell me for each contact of the owner, do they also have her
as a contact?I tried this but it didn't work:
SELECT c.*, EXISTS(SELECT 1 FROM contacts c2 WHERE c2.owner_id =
c1.user_id AND c2.user_id = c1.owner_id)
WHERE c.owner_id = :owner;Thanks!
--
_________________________________
Pulchritudo splendor veritatis.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 4/1/2014 1:27 PM, Robert DiFalco wrote:
Heh, scratch that, the EXISTS query DOES work. Is this the most
efficient way to perform this kind of query? Thanks!
I would try and express that as a left outer join, and use (c2.owner_id
IS NOT NULL) as your boolean field (or something like that)
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general