From: | Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SQL Question |
Date: | 2014-04-01 20:21:17 |
Message-ID: | CAAXGW-yohFpLePLw7aDEwtG7S1zSBFNmseb-L9+mn8KgWf-Ayw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Robert DiFalco | 2014-04-01 20:27:21 | Re: SQL Question |
Previous Message | Si Chen | 2014-04-01 20:11:15 | Re: simple update query stuck |