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.difa...@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! > >
