0

I am working on tinder like app and i want to fetch all match.In my matches table every user has it's own entry.so for one match there are two user and there are total two entry in table.

i tried sql query like given below select user_id, friend_id from matches where is_match = 1 group by user_id,friend_id

with query i am getting below result `

|--------------------------| 
|user_id|friend_id|is_match|
|--------------------------|
|   23  |    24   |    1   |
|--------------------------|
|   24  |    23   |    1   |
|--------------------------|
|   24  |    25   |    1   |
|--------------------------|
|   25  |    24   |    1   |
|--------------------------|
|   25  |    26   |    1   |
|--------------------------|
|   26   |   25   |    1   |
---------------------------

`

I want result like this.

`

|--------------------------|
|user_id|friend_id|is_match|
|--------------------------|
|   23  |    24   |    1   |
|--------------------------|
|   24  |    25   |    1   |
|--------------------------|
|   25  |    26   |    1   |
|--------------------------|

`

Arjun Choudhary
  • 203
  • 3
  • 16

2 Answers2

2

You can get the results you want by sorting the user_id and friend_id values and then selecting only DISTINCT pairs. Note no GROUP_BY should be required.

SELECT DISTINCT LEAST(user_id, friend_id) AS user1, GREATEST(user_id, friend_id) AS user2
FROM matches
WHERE is_match = 1
Nick
  • 138,499
  • 22
  • 57
  • 95
0

If you always have two rows for each pair, then a simple solution is:

SELECT user_id, friend_id
FROM matches
WHERE is_match = 1 AND
      user_id < friend_id;

The use of SELECT DISTINCT (or GROUP BY) makes the query much more expensive.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786