LEFT JOIN

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 14, 2016 at 11:59 am

  #307739

  I'm trying to Load records in a Table called Postal from a table called Postal_Codes_Import

  This is so easy. I have done it numerous time.

  When I do an INNER JOIN I only get 3 records:

  SELECT Post.ID AS ZipCode, Post.CityTownshipID, Post.Latitude, Post.Longitude

  FROM Postal AS Post

  INNER JOIN Postal_Codes_Import AS PostImp ON Post.ID = PostImp.ZipCode

  What am I doing wrong on the LEFT JOIN Statement?

  I get all the records in the Import table but when I do an INNER JOIN I GET 3 Recrds.

  Here is the Code:

  SELECT Postal.ID,PostImp.ZipCode, PostImp.Latitude, PostImp.Longitude,PostImp.CityTownshipID ,PostImp.CountyRegionID, PostImp.StateProvinceID, PostImp.CountryID

  FROM Postal_Codes_Import AS PostImp

  LEFT JOIN Postal AS Postal ON PostImp.ZipCode = Postal.ID

  WHERE ID IS NULL

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 14, 2016 at 12:04 pm

  #1852026

  What's the problem?

  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

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 14, 2016 at 12:09 pm

  #1852029

  That would help if I included the error. :blush:

  Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (B5A 5B4).

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  January 14, 2016 at 12:14 pm

  #1852030

  Why would the Id be the same as the ZipCode?

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  January 14, 2016 at 12:16 pm

  #1852031

  The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.

  You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  January 14, 2016 at 12:17 pm

  #1852032

  Like what it says: "Cannot insert duplicate key in object 'dbo.Postal'."

  Perhaps you're attempting to insert a row that already exists in the target table.

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 14, 2016 at 12:20 pm

  #1852034

  Phil Parkin (1/14/2016)

  ---

  Why would the Id be the same as the ZipCode?

  The ZipCode is named ID.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 14, 2016 at 12:21 pm

  #1852035

  Phil Parkin (1/14/2016)

  ---

  The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.

  You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.

  So what is the syntax?

  Thanks.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 14, 2016 at 12:23 pm

  #1852036

  Eric M Russell (1/14/2016)

  ---

  Like what it says: "Cannot insert duplicate key in object 'dbo.Postal'."

  Perhaps you're attempting to insert a row that already exists in the target table.

  That is correct.

  I'm trying to exclude them.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  January 14, 2016 at 12:28 pm

  #1852038

  Welsh Corgi (1/14/2016)

  ---

  Phil Parkin (1/14/2016)

  ---

  The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.

  You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.

  So what is the syntax?

  Thanks.

  INSERT [target] (columns)

  SELECT (columns)

  FROM [source]

  WHERE NOT EXISTS (select 1 from [target] where target.(PK) = source.(PK))

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  January 14, 2016 at 12:29 pm

  #1852039

  Welsh Corgi (1/14/2016)

  ---

  Phil Parkin (1/14/2016)

  ---

  The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.

  You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.

  So what is the syntax?

  Thanks.

  Here you go: http://bfy.tw/3jNm

  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

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 14, 2016 at 12:30 pm

  #1852040

  Perhaps a Data Type issue?

  Table Structure:

  CREATE TABLE [dbo].[Postal](

  [ID] [varchar](16) NOT NULL,

  [Latitude] [float] NOT NULL,

  [Longitude] [float] NOT NULL,

  [CityTownshipID] [int] NOT NULL,

  [CountyRegionID] [int] NULL,

  [StateProvinceID] [int] NOT NULL,

  [CountryID] [int] NOT NULL,

  CONSTRAINT [PK_Postal] 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]

  CREATE TABLE [dbo].[Postal_Codes_Import](

  [ZipCode] [nvarchar](255) NULL,

  [CityName] [nvarchar](255) NULL,

  [Longitude] [float] NULL,

  [Latitude] [float] NULL,

  [CityTownshipID] [int] NULL,

  [CountyRegionID] [int] NULL,

  [StateProvinceID] [int] NULL,

  [CountryID] [int] NULL

  ) ON [PRIMARY]

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  January 14, 2016 at 12:43 pm

  #1852047

  Welsh Corgi (1/14/2016)

  ---

  That would help if I included the error. :blush:

  Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (B5A 5B4).

  I'm assuming that you've checked that there aren't duplicates in table that your importing.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 14, 2016 at 12:48 pm

  #1852049

  Yes there are 3 duplicates when I do a INNER JOIN but I exclude them in the Insert and I keep getting PK Violations. :unsure:

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  January 14, 2016 at 12:50 pm

  #1852050

  Phil Parkin (1/14/2016)

  ---

  The WHERE NOT EXISTS () construction often performs better than using a LEFT JOIN – I would recommend a test.

  You should include table schemas in your code to avoid the generation of unnecessary cache-miss events.

  I execute the following code:

  SELECT *

  FROM Postal_Codes_Import

  WHERE NOT EXISTS (SELECT *

  FROM Postal

  WHERE Postal.ID = Postal_Codes_Import.ZipCode);

  And it excludes the 3 records.

  Weird.

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/