LEFT JOIN

Question

Post reply

LEFT JOIN

Viewing 15 posts - 16 through 30 (of 43 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I execute the following Query:

INSERT INTO POSTAL (ID,Latitude, ID.Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID)

SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID

FROM Postal_Codes_Import

WHERE NOT EXISTS (SELECT *

FROM Postal

WHERE Postal.ID = Postal_Codes_Import.ZipCode);

I get the following error:

Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (K0E 1T1).

I execute the following:

SELECT ID

FROM Postal

WHERE ID = 'B5A 5E8'

And it does not return any records.

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: 246965 More actions · Answer 2

Welsh Corgi (1/14/2016)
I execute the following Query:
INSERT INTO POSTAL (ID,Latitude, ID.Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID)
SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID
FROM Postal_Codes_Import
WHERE NOT EXISTS (SELECT *
FROM Postal
WHERE Postal.ID = Postal_Codes_Import.ZipCode);
I get the following error:
Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (K0E 1T1).
I execute the following:
SELECT ID
FROM Postal
WHERE ID = 'B5A 5E8'
And it does not return any records.

What does ID.Longitude refer to in the above?

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

It was a typo. I removed it and tried to insert but I still get the error listed above when attempting to Execute the Insert.

INSERT INTO POSTAL (ID, Latitude, Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID)

SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID

FROM Postal_Codes_Import

WHERE ZipCode NOT IN ('T0A 0A1','T0A 0A2','T8T 0W5','B5A 0B1','B5A 5A2','B5A 5A5','B5A 5A8','B5A 5B1','B5A 5B3','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/

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

If you want to INSERT rows for new ZipCodes and UPDATE columns for existing ZipCodes, then that's what's called an "UPSERT" or "Type 1 Change". You can implement this using a single MERGE operation.

https://www.resultdata.com/using-the-merge-statement-with-a-type-1-slowly-changing-dimension/

merge Postal

using Postal_Codes_Import as PostImp

on PostImp.ZipCode = Postal.ID

when not matched then

insert values

(

PostImp.ZipCode

, PostImp.Latitude

, PostImp.Longitude

, PostImp.CityTownshipID

, PostImp.CountyRegionID

, PostImp.StateProvinceID

, PostImp.CountryID

)

when matched then

update set

Latitude = PostImp.Latitude

, Longitude = PostImp.Longitude

, CityTownshipID = PostImp.CityTownshipID

, CountyRegionID = PostImp.CountyRegionID

, StateProvinceID = PostImp.StateProvinceID

, CountryID = PostImp.CountryID

;

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

drew.allen SSC Guru Points: 76988 More actions · Answer 5

Welsh Corgi (1/14/2016)
I execute the following:
SELECT ID
FROM Postal
WHERE ID = 'B5A 5E8'
And it does not return any records.

And what do you get when you execute the following code

SELECT ZipCode

FROM Postal_Codes_Import

WHERE ZipCode = 'B5A 5E8'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

Welsh Corgi (1/14/2016)
I execute the following Query:
INSERT INTO POSTAL (ID,Latitude, ID.Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID)
SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID
FROM Postal_Codes_Import
WHERE NOT EXISTS (SELECT *
FROM Postal
WHERE Postal.ID = Postal_Codes_Import.ZipCode);
I get the following error:
Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (K0E 1T1).
I execute the following:
SELECT ID
FROM Postal
WHERE ID = 'B5A 5E8'
And it does not return any records.

Does this return anything?

SELECT COUNT(*), Postal_Codes_Import.ZipCode

FROM Postal_Codes_Import

WHERE NOT EXISTS (SELECT *

FROM Postal

WHERE Postal.ID = Postal_Codes_Import.ZipCode)

GROUP BY Postal_Codes_Import.ZipCode

HAVING COUNT(*)>1

If so, then you have duplicates in your import table, and you'll have to resolve that.

Cheers!

EDIT: I see Drew's trying to get you to the same place 🙂

drew.allen SSC Guru Points: 76988 More actions · Answer 7

Welsh Corgi (1/14/2016)
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:

An INNER JOIN won't give you the situation that I'm expecting. You're looking for situations where the code does not exist in the target table and there are duplicates in the source table. An inner join will exclude those, because the code doesn't exist in the target table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Jacob Wilkins (1/14/2016)
Welsh Corgi (1/14/2016)
I execute the following Query:
INSERT INTO POSTAL (ID,Latitude, ID.Longitude, CityTownshipID, CountyRegionID,StateProvinceID,CountryID)
SELECT ZipCode, Latitude, Longitude,CityTownshipID , CountyRegionID, StateProvinceID, CountryID
FROM Postal_Codes_Import
WHERE NOT EXISTS (SELECT *
FROM Postal
WHERE Postal.ID = Postal_Codes_Import.ZipCode);
I get the following error:
Violation of PRIMARY KEY constraint 'PK_Postal'. Cannot insert duplicate key in object 'dbo.Postal'. The duplicate key value is (K0E 1T1).
I execute the following:
SELECT ID
FROM Postal
WHERE ID = 'B5A 5E8'
And it does not return any records.
Does this return anything?
SELECT COUNT(*), Postal_Codes_Import.ZipCode
FROM Postal_Codes_Import
WHERE NOT EXISTS (SELECT *
FROM Postal
WHERE Postal.ID = Postal_Codes_Import.ZipCode)
GROUP BY Postal_Codes_Import.ZipCode
HAVING COUNT(*)>1
If so, then you have duplicates in your import table, and you'll have to resolve that.
Cheers!
EDIT: I see Drew's trying to get you to the same place 🙂

I tried the same thing and I was going to post but you beat me..

It returns 126 records however when I query the Postal Table no records are found.

I may have something to do with length of the column?

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/

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

It doesn't matter that there are no matching rows in the postal table.

If that query returns 126 rows, that means there are 126 ZipCodes in the Import table that occur on more than 1 row. So, even though there are no matching rows in the Postal table, if you have 2+ rows with 'B5A 5E8' in the Import table, then your current INSERTs will try to INSERT it multiple times, leading to the error you're seeing.

You'll have to remove the duplicates from the Import table, or filter out the duplicates in the INSERT.

Cheers!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

The following returns 126 records.

So the problem is that there arerror in the Import tABLE.

SELECT ZipCode , COUNT(*) AS RecordCount

FROM Postal_Codes_Import

GROUP BY ZipCode

HAVING COUNT (*) > 1

I have to get rid of the duplicates

ZipCodeRecordCount
B5A 0B12
B5A 5A22
B5A 5A52
B5A 5A83
B5A 5B12
B5A 5B32
B5A 5B42
B5A 5E82
B5A 5G12
B5A 5G33
B5A 5G54
B5A 5G72
B5A 5G82
B5A 5H22
B5A 5H32
B5A 5H52
B5A 5H82
B5A 5H92
B5A 5J14
B5A 5J32
B5A 5J42
B5A 5J62
B5A 5J73
B5A 5J82
B5A 5K22
B5A 5K42
B5A 5K53
B5A 5K62
B5A 5K72
B5A 5K82
B5A 5L14
B5A 5L32
B5A 5L52
B5A 5L63
B5A 5L73
B5A 5L92
B5A 5M72
B5A 5M82
B5A 5M92
B5A 5N12
B5A 5N34
B5A 5N42
B5A 5N62
B5A 5N82
B5A 5N92
B5A 5P23
B5A 5P82
B5A 5P92
B5A 5R12
B5A 5R33
B5A 5R52
B5A 5X93
C0A 1H15
C0A 1H35
C0A 1H65
C0A 1H74
C0A 1H93
C1B 0S72
C1B 0T92
C1B 0W52
C1B 3M82
C1B 3N12
C1B 3R72
C1C 0J82
C1C 0L42
C1E 0T42
C1E 0X52
C1E 2N42
C1E 2V52
C1E 2V72
C1E 2W92
E3G 7N22
E4S 0E82
G0G 0G02
G0N 1E12
G9X 0A72
K0E 1T12
K0L 1M12
K0M 1J22
K0M 2L12
K7S 0E12
K9J 0C72
K9J 0G62
L7B 0A12
L7B 0G12
L9M 0H12
L9Y 0R72
N0N 1J32
N0N 1J43
N0N 1J52
N3A 0A72
N8P 0A92
P0T 1C12
P6A 0A42
P6A 0A52
P6A 0B92
P7G 0S32
P7G 0W62
P7G 0W72
P9N 0J22
S0A 0L12
S0J 2E12
S0K 4T12
T0E 2K12
T0G 1L22
T4A 0M92
T4B 2T33
T4B 2T42
T4G 0G92
T4G 0H32
T4G 0J72
T4G 0K12
T4G 0K82
T7E 3A12
T7E 3E22
T8X 4G82
T8X 4L62
T8X 4P32
T8X 4R32
V0J 1Y22
V0J 1Y42
V0J 1Y52
V0K 2S12
V1G 0B92
V1G 0C22
V3C 0B82

Thanks everyone for your help.

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 · Answer 11

A possibly bad idea is to change an option in your PK index. Specifically IGNORE_DUP_KEY.

This will prevent errors from happening when inserting duplicates to a table, but will also hide the problems you may want to correct.

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 · Answer 12

Thanks.

Does anyone have a script that could be adapted to correct the duplicate issue.

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: 76988 More actions · Answer 13

Welsh Corgi (1/14/2016)
Thanks.
Does anyone have a script that could be adapted to correct the duplicate issue.

Surely you've been around long enough to know how to set up a CTE with ROW_NUMBER to only retrieve one record per ZipCode.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

I need to create a new table that has an Identity Column so that I can delete the duplicates.

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/

Lowell SSC Guru Points: 323518 More actions · Answer 15

if your source has multiple lat/long for a postal code, you'd get the error;

you need to limit to a single "missing" postal code

WITH MySource

AS

(SELECT ROW_NUMBER() OVER (Partition By PostImp.ZipCode ORDER BY PostImp.ZipCode) AS RW,*

FROM Postal_Codes_Import

)

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

FROM MySource

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

WHERE MySource.RW = 1

AND Postal.ID IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!