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

  • 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.

  • 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)

  • 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?

  • 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]

  • 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)

  • 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.

  • Thank you everyone!

    I appreciate all answers.

  • Thanks Jeff, appreciate it.

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

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