I have a more complex requirement of the Cartesian product...
I’m familiar with CROSS JOIN.

E.g. I want to have all the possible combinations of 2-student groups for a project

Table_A – Grade, Class, Name
1, 1A, John
1, 1A, Mike
1, 1B, Sam
2, 2A, Pete
2, 2B, Paul

SELECT A.Name, B.Name
FROM Table_A as A
CROSS JOIN TABLE_B as B


Result:
John, Mike
John, Sam
John, Pete
John, Paul
Mike, Sam
Mike, Pete
Mike, Paul
Sam, Pete
Sam, Paul
Etc..

***

So here is what I want. Rather than pair up ALL students, i’d like to only pair up the ones in the same class.

Required result:
John, Mike

This is the only record being returned since it is the only pair that are in the same class (1A).

Can I use CROSS JOIN to do this?


Thanks

you can do it either with a CROSS JOIN --or with an INNER JOIN --

ive included a.name != b.name to avoid having students paired with themselves e.g. John, John

SELECT a.name, b.name
FROM Table_A AS a
CROSS JOIN Table_A AS b
WHERE a.class = b.class
and a.name != b.name

result:
Mike, John
John, Mike

is there an efficient way to have the query as a combination rather than permutation i.e. Mike, John is the same as John, Mike so the desired result is simply:
Mike, John

thanks again

yup --

awesome, thanks for the help and the prompt reply