Need to query db to return all instances for Canada. (Country, State, City)

Question

Need to query db to return all instances for Canada. (Country, State, City)

peerless

Ten Centuries

Points: 1114
More actions
February 1, 2014 at 2:39 pm

#281823

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.

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

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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 1

Except for the unnecessary verbosity caused by 3 part naming instead of the preferred 2 part naming (use synonyms instead of 3 or 4 part naming), it seems ok as written. Are you having a problem with it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

peerless Ten Centuries Points: 1114 More actions · Answer 2

hi jeff,

Cannot insert the value NULL into column 'City', table 'DB_61318_itweb.dbo.Location'; column does not allow nulls. INSERT fails.

<code>

create table country(country_code nvarchar(2), country_name nvarchar(255))

create table states(country_code nvarchar(2),state_code nvarchar(20),state_name nvarchar(255))

create table weblocations (country_code nvarchar(2), state_code nvarchar(20),city_name nvarchar(255),timezoneid varchar(255))

INSERT INTO Location

([Country],[City], [State] )

SELECT

country.country_name AS [Country],

weblocations.city_name AS [City],

states.state_name AS [State]

FROM

country

LEFT JOIN

weblocations ON country.country_code = weblocations.country_code

LEFT JOIN

states ON weblocations.state_code = states.state_code;

CREATE TABLE [dbo].[Location](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Country] [nvarchar](max) NOT NULL,

[City] [nvarchar](max) NOT NULL,

[State] [nvarchar](max) NOT NULL,

CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

</code>

What should I do?

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

peerless (2/1/2014)
...
What should I do?

You should investigate the results from the SELECT part of your INSERT query, not just this time, but every time. The same applies to UPDATEs and DELETEs.

SELECT

c.country_name AS [Country],

w.city_name AS [City],

s.state_name AS [State]

FROM country c

LEFT JOIN weblocations w ON c.country_code = w.country_code

LEFT JOIN states s ON w.state_code = s.state_code;

Changing the LEFT JOINS to INNER JOINS will almost certainly eliminate the nulls at the risk of eliminating rows which you might want.

This query is very different to the one you originally posted, reformatted like so:

USE DB_61318_itweb

GO

SELECT

---c.CountryCode,

c.Country as [Country],

r.Region as [State],

--r.Admin1Code,

g.Name AS City

FROM dbo.itweb_Countries c

INNER JOIN dbo.itweb_Regions r

ON c.CountryCode = r.CountryCode

INNER JOIN dbo.itweb_GeoNames g

ON r.Admin1Code = g.Admin1Code

WHERE c.CountryCode = 'CA'

ORDER BY r.Region, g.Name

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

peerless (2/1/2014)
What should I do?

I'm with Chris on this one. Stop making people try to eat a rolling donut by changing your code from post to post. And next time, post your error message up front instead of asking people if they see something wrong with your code. It's a total waste of everyone's time (including yours, I'm telling your boss! :-P) and downright rude especially when you knew you were getting an error message.

Shifting gears, read the error message you got and investigate the problem. What do YOU think the problem is when you get that kind of an error about NULLs?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 5

Chris has the solution unless you only want the distinct values (in other words, if you have 7600 records from Calgary, you only want one result row for the city), in which case you would have to use DISTINCT and ISNULL with a left join, sort of like this:

WITH (

SELECT

DISTINCT

ISNULL(c.Country,'') as [Country],

ISNULL(r.Region,'') as [State],

--r.Admin1Code,

ISNULL(g.Name,'') AS City

FROM dbo.itweb_Countries c

INNER JOIN dbo.itweb_Regions r

ON c.CountryCode = r.CountryCode

INNER JOIN dbo.itweb_GeoNames g

ON r.Admin1Code = g.Admin1Code

WHERE c.CountryCode = 'CA'

ORDER BY r.Region, g.Name

) as Q

SELECT

Country

,State

,City

FROM Q

where Country != ''

and City != ''

and State != ''

Thanks,

John.

peerless Ten Centuries Points: 1114 More actions · Answer 6

peerless

Ten Centuries

Points: 1114

February 3, 2014 at 11:37 am

#1685592

Thank you everyone!

I appreciate all answers.

peerless Ten Centuries Points: 1114 More actions · Answer 7

peerless

Ten Centuries

Points: 1114

February 3, 2014 at 11:39 am

#1685594

Thanks Jeff, appreciate it.