# 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 (
);

, ('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
FROM user AS u1
CROSS
``````

Which gives

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

I want to produce the following resultset

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

``````

Thanks

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

``````
SELECT DISTINCT
CASE
END AS name1
, CASE
END AS name2
FROM user AS u1

``````

``````SELECT
FROM
`user` as u1 JOIN `user` as u2
``````

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

more intuitive

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

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

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

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