CODEWITHSUNDEEP
mysql
skobaljic
skobaljic

Reputation: 9634

Find columns which come in pair with multiple/various other-column values

I have a points table, where important columns are:

id         userid        orderid
1          10            150
2          10            150
3          15            151
4          12            152
5          11            152

I need to find all orderid which have multiple/various userid. The result would be:

id         userid        orderid
4          12            152
5          11            152

I can do it in PHP, but I hope someone have time to help me with mysql query. What I have tried so far is probably irrelevant.

Upvotes: 1

Views: 43

Answers (2)

FirstOne
FirstOne

Reputation: 6217

If you want to get just the rows that have same orderid but different userid, use this:

SELECT P1.* FROM points P1 
INNER JOIN points P2 
    ON P1.orderid = P2.orderid and P1.id != P2.id and P1.userid != p2.userid;

Note that this first select returns what you expect in your question:

+----+--------+---------+
| id | userid | orderid |
+----+--------+---------+
|  4 |     12 |     152 |
|  5 |     11 |     152 |
+----+--------+---------+

Now, if you want to return ANY orderid that is the same, regardless of userid, use this:

SELECT P1.* FROM points P1 
INNER JOIN points P2 
    ON P1.orderid = P2.orderid and P1.id != P2.id;

In this case, it won't exclude the result with same id, returning

+----+--------+---------+
| id | userid | orderid |
+----+--------+---------+
|  1 |     10 |     150 |
|  2 |     10 |     150 |
|  4 |     12 |     152 |
|  5 |     11 |     152 |
+----+--------+---------+

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

Use COUNT(DISTINCT) and HAVING to find orderid with multiple various userid.

SqlFiddleDemo

SELECT t.*
FROM tab t
JOIN (SELECT orderid, COUNT(DISTINCT userid) 
      FROM tab
      GROUP BY orderId
      HAVING COUNT(DISTINCT userid) > 1) AS sub
  ON t.orderid = sub.orderid
ORDER BY t.id

Upvotes: 1

Related Questions