Hi, 

i would like to get help writing a cross join query:

one table: Cities(City_name, X_coordinate, Y_coordinate)

the result should be all combinations without reverse column returns

SELECT * FROM [dbo].[Cities] as P1
Cross JOIN [dbo].[cities] as p2
where (p1.City_name != p2.City_name) and ???

for example if there are three Cities as A,B,C the result should be A->B, A->C, B->C (without the returns B->A, C->A, C->B)

thanks,

Ben

• Moved by Lydia ZhangMicrosoft contingent staff, Moderator 5 hours 2 minutes ago more appropriate

Hi Ben_CH,

Visakh16's query would be good to acheve your requirement. I saw the where condition and the description said "without reverse column", then the INNER JOIN would lead to your goal, you don't have to use CROSS JOIN.

If you have any question, feel free to let me know.