select statement many-to-many - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select statement many-to-many

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2012-05-31 : 05:13:02

Dear Expert,

I have following table schema

FromCountry int
FromCity int
ToCountry int
ToCity int
Amount int

I want to select data with the following parameters.
FromCountry : X
FromCity : All (all cities in that country X)
ToCountry : Y
ToCity : All (all cities in that country Y)

So if i have 2 cities in country X and 3 cities in country Y i should get the 2*3 = 6 Records.

Performance is my concern, could you help me write such query.

Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-05-31 : 05:37:23

select t1.fromcity, t2.tocity
from tbl t1
cross join tbl t2
where t1.fromcountrty = 'x'
and t2.tocountrty = 'y'

or

select t1.fromcity, t2.tocity
from (select * from tbl where fromcountrty = 'x' and tocountrty = 'y') t1
cross join (select * from tbl where fromcountrty = 'x' and tocountrty = 'y') t2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

amodi
Yak Posting Veteran

83 Posts

Posted - 2012-06-02 : 01:52:38

Thanks nigelrivett for your kind reply!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources