Compare one field between two rows in the same table

Question

I have a small table which contains group memberships to which I am struggling to find a query.

   uid     groupid     userid
    1         2          5
    2         2          6
    3         1          2
    4         3          8
    5         4          7

I was wondering if it is possible to return TRUE if two given user IDs where in the same group?

@HartCO It sounds more like he wants to accept two `UserId` params, and return 1 or 0 depending on if those two ID's have the same `GroupId` — Siyual, Feb 12 '15 at 21:26
yes, I want to identify whether `groupid` is the same for 2 given `userid` — Arturski, Feb 12 '15 at 21:27
Is `userid UNIQUE NOT NULL` and `groupid NOT NULL` in your table, like the sample data seems to suggest? As *always*, a table definition (`\d tbl` in psql) would clarify things. — Erwin Brandstetter, Feb 13 '15 at 04:33

arnoudhgz · Answer 1 · 2015-02-13T07:51:36.917

2

SELECT groupid, CASE WHEN COUNT(distinct userid) > 1 THEN "TRUE" ELSE "FALSE" END
FROM my_table
WHERE userid IN ('x', 'y')
GROUP BY groupid

Note the x and y should be replaced with the given userids

TRUE:

SELECT groupid, CASE WHEN COUNT(distinct userid) > 1 THEN 'TRUE' ELSE 'FALSE' END
FROM my_table
WHERE userid IN (5,6)
GROUP BY groupid

FALSE:

SELECT groupid, CASE WHEN COUNT(distinct userid) > 1 THEN 'TRUE' ELSE 'FALSE' END
FROM my_table
WHERE userid IN (5,2)
GROUP BY groupid

http://sqlfiddle.com/#!15/3f156/1

edited Feb 13 '15 at 07:51

answered Feb 12 '15 at 21:30

arnoudhgz

1,284
9
17

The fiddle is for MySQL, but the question is for Postgres, where double-quotes are for identifiers (as per SQL standard) and your syntax is invalid. – Erwin Brandstetter Feb 13 '15 at 04:52
Sorry by default I'm in Mysql modus :(, updated the everything to Postgres – arnoudhgz Feb 13 '15 at 07:52
Thank you this worked like a charm... i tested it on gpadmin 3 and the TRUE and FALSE did not require any double quotes or apostrophes. If a user is a member of more than 1 group it returns 2 rows, one as false and one as true. Thank you! – Arturski Feb 19 '15 at 14:55

score 2 · Accepted Answer · answered Feb 12 '15 at 21:38

2

The following gets all groups that have two given members:

select groupid
from table t
where userid in ($userid1, $userid2)
group by groupid
having count(distinct userid) = 2;

You can turn this into a boolean if you like:

select (case when count(*) > 0 then true else false end)
from (select groupid
      from table t
      where userid in ($userid1, $userid2)
      group by groupid
      having count(distinct userid) = 2
     ) g;

answered Feb 12 '15 at 21:38

Gordon Linoff

1,242,037
58
646
786

Thank you this has also worked great and gave me the correct result. I will experiment with both answers and see which one fits better on a larger scale. – Arturski Feb 19 '15 at 15:17
How would I get around it passing out false if you feed it 2 same IDs? – Arturski Mar 04 '15 at 16:06
@Arturski . . . You would need logic to change the 2 to a 1 . . . perhaps `having count(distinct userid) = (case when @userid1 = $userid2 then 1 else 2 end)`. – Gordon Linoff Mar 05 '15 at 01:09

score 0 · Answer 3 · edited May 23 '17 at 12:12

`UNIQUE userid`

Each user can only have at most one entry (like the question seems to ask).

SELECT (SELECT groupid FROM tbl WHERE userid = 5)
     = (SELECT groupid FROM tbl WHERE userid = 6);

Assuming useridis UNIQUE.
Returns TRUE or FALSE exactly like requested.
- or NULL if a userid is not found or groupid is NULL.

`UNIQUE (userid, groupid)`

Each user can only have multiple entries (as clarified in the comment):

Share all groups?

SELECT EXISTS (
   SELECT 1
   FROM      (SELECT groupid FROM tbl2 WHERE userid = 5) a
   FULL JOIN (SELECT groupid FROM tbl2 WHERE userid = 6) b USING (groupid)
   WHERE a.groupid IS NULL OR
         b.groupid IS NULL
   ) AS share_all;

Share at least one group?

SELECT EXISTS (
   SELECT groupid FROM tbl2 WHERE userid = 8
   INTERSECT
   SELECT groupid FROM tbl2 WHERE userid = 9
   ) AS share_min_one;

Or

SELECT EXISTS (
  SELECT 1
  FROM  (SELECT groupid FROM tbl2 WHERE userid = 5) a
  JOIN  (SELECT groupid FROM tbl2 WHERE userid = 6) b USING (groupid)
  ) AS share_min_one;

Share exactly one group?

SELECT count(*) = 1 AS share_exactly_one
FROM  (SELECT groupid FROM tbl2 WHERE userid = 5) a
JOIN  (SELECT groupid FROM tbl2 WHERE userid = 6) b USING (groupid);

SQL Fiddle with better test data.

All of these queries are fast with an index on userid. Faster with a multicolumn index on (userid, groupid) in Postgres 9.2+.

Ultimately this is a case of relational-division. Here's an arsenal of query techniques:

How to filter SQL results in a has-many-through relation

This query works but only in cases where users cannot be part of more than one group. My fault for not clarifying this at the start. — Arturski, Feb 19 '15 at 15:26
@Arturski: The question asks `if two given user IDs where in the same group`. Singular indicates *one* group, the dummy data supports that and you did not answer my RFC at first. Now, if a single user can be in multiple groups, you need to clarify more: Is `(groupid, userid)` unique? Do you want users that share all groups, exactly one group (like your question still indicates) or at least one group? That's really a different question. I added some more answers. — Erwin Brandstetter, Feb 19 '15 at 23:37

Compare one field between two rows in the same table

3 Answers3

UNIQUE userid

UNIQUE (userid, groupid)

`UNIQUE userid`

`UNIQUE (userid, groupid)`