cross join query

Question

Post reply

cross join query

bchemouny

Valued Member

Points: 61
More actions
July 19, 2015 at 4:36 pm

#398763

Hi there,
i would like to get help writing a cross join query with 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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

create table #t1

( id int identity(1,1) ,

city varchar(128)

)

create table #t2

( id int identity(1,1),

city varchar(128)

)

insert into #t1

select 'Dubai'

union all

select 'Delhi'

union all

select 'Denver'

insert into #t2

select 'Dubai'

union all

select 'Delhi'

union all

select 'Denver'

select * from #t1 t1

cross join #t2 t2

where t1.city <> t2.city

Jayanth Kurup[/url]

bchemouny Valued Member Points: 61 More actions · Answer 2

Hi Jayanth,

It's still returns : Dubai Delhi , Delhi Dubai

(in the oposite order)

Ben

Chris Wooding SSCarpal Tunnel Points: 4558 More actions · Answer 3

Try changing p1.City_Name != p2.City_Name to p1.City_Name > p2.City_Name - that way you will only pick up the link in one direction.

bchemouny Valued Member Points: 61 More actions · Answer 4

bchemouny

Valued Member

Points: 61

July 20, 2015 at 1:41 am

#1813586

working 🙂 Thanks a lot...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Try the following article.

http://www.sqlservercentral.com/articles/sql+n-Tuples/89809/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 6

Sorry I misread the OPs question here is the updated script as Chris mentioned

create table #t1

( id int identity(1,1) ,

city varchar(128)

)

create table #t2

( id int identity(1,1),

city varchar(128)

)

insert into #t1

select 'Dubai'

union all

select 'Delhi'

union all

select 'Denver'

insert into #t2

select 'Dubai'

union all

select 'Delhi'

union all

select 'Denver'

select * from #t1 t1

cross join #t2 t2

where t1.city <> t2.city

and t1.city > t2.city

Jayanth Kurup[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Jayanth_Kurup (7/20/2015)
select * from #t1 t1
cross join #t2 t2
where t1.city <> t2.city
and t1.city > t2.city

Do you realize that the first condition is not needed?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 8

There's also no need to write this as a CROSS JOIN... It's actually what Jeff would refer to as a "triangular join".

SELECT

CityName

INTO #City

FROM (VALUES ('Jax'),('Orlando'),('Miami'),('Tally'),('Appalach'),('Lake City'),('Panama City')) City (CityName)

SELECT

c1.CityName AS CityName_1,

c2.CityName AS CityName_2

FROM

#City c1

JOIN #City c2

ON c1.CityName < c2.CityName

ORDER BY-- Not needed just makes it easier to verify results

c1.CityName,

c2.CityName