I have to retrieve a query. - 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

I have to retrieve a query.

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2014-02-01 : 16:31:55

I have the following will return
Country, State, City

I need to make sure that this query is ok.
<code>
SELECT
---DB_61318_itweb.dbo.itweb_Countries.CountryCode,
DB_61318_itweb.dbo.itweb_Countries.Country as [Country],
DB_61318_itweb.dbo.itweb_Regions.Region as [State],
--DB_61318_itweb.dbo.itweb_Regions.Admin1Code,
DB_61318_itweb.dbo.itweb_GeoNames.Name AS City
FROM
DB_61318_itweb.dbo.itweb_Countries INNER JOIN
DB_61318_itweb.dbo.itweb_Regions ON DB_61318_itweb.dbo.itweb_Countries.CountryCode = DB_61318_itweb.dbo.itweb_Regions.CountryCode INNER JOIN
DB_61318_itweb.dbo.itweb_GeoNames ON DB_61318_itweb.dbo.itweb_Regions.Admin1Code = DB_61318_itweb.dbo.itweb_GeoNames.Admin1Code

WHERE (DB_61318_itweb.dbo.itweb_Countries.CountryCode = 'CA')
order by DB_61318_itweb.dbo.itweb_Regions.Region, DB_61318_itweb.dbo.itweb_GeoNames.Name
</code>

Tables involved
<code>
CREATE TABLE [dbo].[itweb_Regions](
[CountryCode] [char](2) NOT NULL,
[Admin1Code] [varchar](20) NOT NULL,
[Region] [varchar](1024) NOT NULL
) ON [PRIMARY]

GO

------------------------

CREATE TABLE [dbo].[itweb_GeoNames](
[GeonameID] [int] NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[AnsiName] [varchar](200) NULL,
[AlternateNames] [nvarchar](max) NULL,
[Latitude] [float] NOT NULL,
[Longitude] [float] NOT NULL,
[FeatureClass] [char](1) NULL,
[FeatureCode] [varchar](10) NULL,
[CountryCode] [char](2) NULL,
[CC2] [varchar](60) NULL,
[Admin1Code] [varchar](20) NULL,
[Admin2Code] [varchar](80) NULL,
[Admin3Code] [varchar](20) NULL,
[Admin4Code] [varchar](20) NULL,
[Population] [bigint] NOT NULL,
[Elevation] [int] NULL,
[GTopo30] [int] NULL,
[Timezone] [varchar](50) NULL,
[ModificationDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[itweb_Countries](
[CountryCode] [char](2) NOT NULL,
[CountryAlpha3] [varchar](3) NOT NULL,
[CountryNumeric] [int] NOT NULL,
[Fips] [varchar](2) NULL,
[Country] [varchar](1024) NOT NULL,
[Capital] [varchar](1024) NULL,
[Area] [int] NULL,
[Population] [int] NULL,
[ContinentID] [varchar](2) NOT NULL
) ON [PRIMARY]

GO

-----------------------------------------
</code>

Any help would be awesome.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-02 : 01:28:32

Can you show some sample data and then explain what you're expecting as output? otherwise we cant suggest whether query is correct for your exact requirement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources