Cross Join Unique Combinations

How can I remove duplicate combinations from a cross join result? Each person should be paired up with another person only once.

Given the following data:

create table user (
    username varchar(30) not null
);

insert into user values ('Adrian')
                      , ('Frank')
                      , ('Jimbob')
                      , ('Joseph');

A basic cross join removing equal pairs gave this. I spent about an hour fighting with different methods to filter out the combinations but had no luck in the end.

SELECT u1.username AS name1
     , u2.username AS name2
  FROM user AS u1
CROSS
  JOIN user AS u2
WHERE u1.username <> u2.username;

Which gives

+--------+--------+
| name1  | name2  |
+--------+--------+
| Frank  | Adrian |
| Jimbob | Adrian |
| Joseph | Adrian |
| Adrian | Frank  |
| Jimbob | Frank  |
| Joseph | Frank  |
| Adrian | Jimbob |
| Frank  | Jimbob |
| Joseph | Jimbob |
| Adrian | Joseph |
| Frank  | Joseph |
| Jimbob | Joseph |
+--------+--------+
12 rows in set (0.00 sec)

I want to produce the following resultset

+--------+--------+
| name1  | name2  |
+--------+--------+
| Frank  | Adrian |
| Jimbob | Adrian |
| Joseph | Adrian |
| Jimbob | Frank  |
| Joseph | Frank  |
+--------+--------+

Thanks

Try this (it might not be the most effective way, but it should work)


SELECT DISTINCT
    CASE
      WHEN u1.username < AS u2.username
        THEN u1.username
      ELSE u2.username
    END AS name1
  , CASE
      WHEN u1.username < AS u2.username
        THEN u2.username
      ELSE u1.username
    END AS name2
FROM user AS u1
CROSS JOIN user AS u2
WHERE u1.username <> u2.username

What about something like this:

SELECT 
    u1.username as user1,
    u2.username as user2
FROM
    `user` as u1 JOIN `user` as u2
    ON u1.username &gt; u2.username;

excellent, Kailash Badu

except i would’ve used CROSS JOIN WHERE instead of INNER JOIN ON

more intuitive

:slight_smile:

Yep, way better than mine. I knew there had to be a better way :slight_smile:

You guys rock.

Can you explain a little more how the > operator works here? Would it also work if the fields in question were integer fields?

the > operator is the “greater than” operator

it returns TRUE when the thing on the left of it is greater than the thing on the right of it

it works equally well when the things are numbers, strings, or dates

in this case they’re strings

the CROSS JOIN returns both sets of pairs, X and Y as well as Y and X

your objective is to have each pair of X and Y represented only once, and so WHERE X > Y will remove (filter out) half the results, namely those which have Y greater than X

simple, innit :cool:

Ah ok, so it basically just removes half the results because they will always have different values. I wasn’t sure if it was a special string operator because I’ve never seen it used like this before.

Cheers guys :slight_smile:

yep, it is a special string operator

it is also a special date operator

which of the following is true…

[list][]‘curly’ > ‘abbott’
[
]‘2009-12-25’ > ‘2009-02-14’
[*]1000 > 937
[/list]
that’s right, all of dem :cool: