Search Postgresql Archives

SQL Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux