Thread: 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:
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.
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