LEFT JOIN Multiple Tables Bad Data

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

LEFT JOIN Multiple Tables Bad Data

Author

Topic

jcervantes
Starting Member

14 Posts

Posted - 2012-02-09 : 15:42:10

UPDATED Added Details

I have multiple tables I am wanting data from.

FSALES2011 - sales info
SXCO CHAR Company
SXMCU CHAR Buisness Unit
SXAN8 NUMERIC Address Number
SXLITM CHAR Item Number
SXSLSM NUMERIC Sales Person Code
SXDCTO CHAR Order Type
SXSLD1 CHAR Sold To
SXADD1 CHAR Sold To Address
SXRP01 CHAR Division
SXRP02 CHAR Location

F4801 - Additional sales info (Items Sold)
I LEFT JOIN this table to FSALES because FSALES contains multiple types of sales (WO/Jobs) and I want to keep all of the FSALES table throught this whole select statement. So I only want to add the WAWR01 (WO Type) to FSALES where FSALES2011.SXLITM = DIGITS(F4801.WADOCO) The rest can be null or blank.
WADOCO NUMERIC Order Number
WAAN8 NUMERIC Address Number
WAWR01 CHAR () FOR BIT DATA WO Type

F0101 - Additional sales info (customer type)
I do an INNER JOIN here because it has the customer type data and all of the sales data in FSALES will have
a customer type.
ABAN8 NUMERIC Address Number
ABAC15 CHAR () FOR BIT DATA Customer Type Code

F0006 - Additional sales info (work performed)
I LEFT JOIN this table to FSALES because FSALES contains multiple types of sales (WO/Jobs) and I want to keep all of the FSALES table throught this whole select statement. So I only want to add the MCRP08 (Job Type) to FSALES where FSALES2011.SXLITM = SUBSTR(F0006.MCMCU,6,7) The rest can be null or blank.
MCMCU CHAR () FOR BIT DATA Buisness Unit
MCRP08 CHAR () FOR BIT DATA Job Type

Up untill this final join everything looks good. What I want to do from here is add the description DRDL01 where F0101.ABAC15 = F0005.DRKY again not wanting to get rid of any rows of FSALES just adding columns where needed.

F0005 - Definitions for customer type
DRKY CHAR () FOR BIT DATA Customer Type Code
DRDL01 CHAR Customer Type Description

What I am trying to do below is get all of the sales data in FSALES add the customer type column from F0101, the Job type from F0006, WO type from F4801, and lastly the Description for the customer type from F0005.

Everything works in the query until i do the final join. At the point my # rows explodes! from aprox. 70000 to a lot more!


SELECT 	FSALES2011.SXAN8,
		FSALES2011.SXCO AS Company,
		FSALES2011.SXMCU AS BuisinessUnit, 
		FSALES2011.SXLITM AS ItemNumber, 
		FSALES2011.SXSLSM AS SalesPersonCode,
		FSALES2011.SXDCTO AS OrderType,
		FSALES2011.SXSLD1 AS SoldTo,
		FSALES2011.SXADD1 AS Address, 
		FSALES2011.SXRP01 AS Division,
		FSALES2011.SXRP02 AS Location,
		F4801.WAAN8,
		F4801.WAWR01 AS WoType,
		DIGITS(F4801.WADOCO) AS F4801ItemNumber,
		F0101.ABAN8, 
		F0101.ABAC15 AS F0101CustomerType,
		F0006.MCRP08 AS JobType,
		SUBSTR(F0006.MCMCU,6,7) AS F0006BuisnessUnit,
		F0005.DRKY AS UDC,
        F0005.DRDL01 AS Description
FROM S10275AF.SENDTA.F0101 F0101 
INNER JOIN S10275AF.JDEMOD.FSALES2011 FSALES2011 ON F0101.ABAN8 = FSALES2011.SXAN8
LEFT OUTER JOIN S10275AF.SENDTA.F0006 F0006 ON FSALES2011.SXLITM = SUBSTR(F0006.MCMCU,6,7)
LEFT OUTER JOIN S10275AF.SENDTA.F4801 F4801 ON FSALES2011.SXLITM = DIGITS(F4801.WADOCO)
LEFT OUTER JOIN S10275AF.SENCOM.F0005 F0005 ON F0101.ABAC15 = F0005.DRKY

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-09 : 15:55:41

We can't really help with the info you've given, but it seems you have a lot of data in the last table that matches the join criteria.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jcervantes
Starting Member

14 Posts

Posted - 2012-02-09 : 17:07:50

Thanks, I added some additional details.

jcervantes
Starting Member

14 Posts

Posted - 2012-02-10 : 14:46:35

quote:
Originally posted by tkizer

We can't really help with the info you've given, but it seems you have a lot of data in the last table that matches the join criteria.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Ok getting closer!

The issue for additional rows was because I had duplicates in my F0005 table. So now i am trying to filter those out with.

SELECT          FSALES2011.SXAN8,
		FSALES2011.SXCO AS Company,
		FSALES2011.SXMCU AS BuisinessUnit, 
		FSALES2011.SXLITM AS ItemNumber, 
		FSALES2011.SXSLSM AS SalesPersonCode,
		FSALES2011.SXDCTO AS OrderType,
		FSALES2011.SXSLD1 AS SoldTo,
		FSALES2011.SXADD1 AS Address, 
		FSALES2011.SXRP01 AS Division,
		FSALES2011.SXRP02 AS Location,
		FSALES2011.SXCI13+FSALES2011.SXCI14+FSALES2011.SXCI15+FSALES2011.SXCI16+FSALES2011.SXCI17+FSALES2011.SXCI18+FSALES2011.SXCI19+FSALES2011.SXCI20+FSALES2011.SXCI21+FSALES2011.SXCI22+FSALES2011.SXCI23+FSALES2011.SXCI24 AS PurchasePrice,
		FSALES2011.SXAS13+FSALES2011.SXAS14+FSALES2011.SXAS15+FSALES2011.SXAS16+FSALES2011.SXAS17+FSALES2011.SXAS18+FSALES2011.SXAS19+FSALES2011.SXAS20+FSALES2011.SXAS21+FSALES2011.SXAS22+FSALES2011.SXAS23+FSALES2011.SXAS24 AS SalesPrice,
		F4801.WAAN8,
		F4801.WAWR01 AS WoType,
		DIGITS(F4801.WADOCO) AS F4801ItemNumber,
		F0101.ABAN8, 
		F0101.ABAC15 AS F0101CustomerType,
		F0006.MCRP08 AS JobType,
		SUBSTR(F0006.MCMCU,6,7) AS F0006BuisnessUnit,
		F0005.DRKY AS UDC,
              	F0005.DRDL01 AS Description
FROM SENDTA.F0101 F0101 
INNER JOIN JDEMOD.FSALES2011 FSALES2011 ON F0101.ABAN8 = FSALES2011.SXAN8
LEFT OUTER JOIN SENDTA.F0006 F0006 ON FSALES2011.SXLITM = SUBSTR(F0006.MCMCU,6,7)
LEFT OUTER JOIN SENDTA.F4801 F4801 ON FSALES2011.SXLITM = DIGITS(F4801.WADOCO)
LEFT OUTER JOIN SENCOM.F0005 F0005 ON F0101.ABAC15 = F0005.DRKY AND F0005.DRKY != '' AND F0005.DRRT IN ('W1','08') AND F0005.DRSY NOT IN ('30','32','98')

Which isn't adding additional rows, but not my descriptions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 15:49:34

why..whats the issue with descriptions? or are you losing out some descriptions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcervantes
Starting Member

14 Posts

Posted - 2012-02-10 : 16:41:04

quote:
Originally posted by visakh16

why..whats the issue with descriptions? or are you losing out some descriptions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

A little more information on the DB the Tables I am joining on F0005.DRKY is VARCHAR(10) and F0101.ABAC15 is VARCHAR(3)
After going and looking at the data it looks like ABAC15 has some leading spaces so i tried doing an LTTRIM and it still doesnt appear to work any other ideas?

My latest effort is below :)

SELECT          FSALES2011.SXAN8,
		FSALES2011.SXCO AS Company,
		FSALES2011.SXMCU AS BuisinessUnit, 
		FSALES2011.SXLITM AS ItemNumber, 
		FSALES2011.SXSLSM AS SalesPersonCode,
		FSALES2011.SXDCTO AS OrderType,
		FSALES2011.SXSLD1 AS SoldTo,
		FSALES2011.SXADD1 AS Address, 
		FSALES2011.SXRP01 AS Division,
		FSALES2011.SXRP02 AS Location,
		FSALES2011.SXCI13+FSALES2011.SXCI14+FSALES2011.SXCI15+FSALES2011.SXCI16+FSALES2011.SXCI17+FSALES2011.SXCI18+FSALES2011.SXCI19+FSALES2011.SXCI20+FSALES2011.SXCI21+FSALES2011.SXCI22+FSALES2011.SXCI23+FSALES2011.SXCI24 AS PurchasePrice,
		FSALES2011.SXAS13+FSALES2011.SXAS14+FSALES2011.SXAS15+FSALES2011.SXAS16+FSALES2011.SXAS17+FSALES2011.SXAS18+FSALES2011.SXAS19+FSALES2011.SXAS20+FSALES2011.SXAS21+FSALES2011.SXAS22+FSALES2011.SXAS23+FSALES2011.SXAS24 AS SalesPrice,
		F4801.WAAN8,
		F4801.WAWR01 AS WoType,
		DIGITS(F4801.WADOCO) AS F4801ItemNumber,
		F0101.ABAN8, 
		LTRIM(F0101.ABAC15) AS F0101CustomerType,
		F0006.MCRP08 AS JobType,
		SUBSTR(F0006.MCMCU,6,7) AS F0006BuisnessUnit,
		LTRIM(F0005.DRKY) AS UDC,
             	F0005.DRDL01 AS Description
FROM SENDTA.F0101 F0101 
INNER JOIN JDEMOD.FSALES2011 FSALES2011 ON F0101.ABAN8 = FSALES2011.SXAN8
LEFT OUTER JOIN SENDTA.F0006 F0006 ON FSALES2011.SXLITM = SUBSTR(F0006.MCMCU,6,7)
LEFT OUTER JOIN SENDTA.F4801 F4801 ON FSALES2011.SXLITM = DIGITS(F4801.WADOCO)
LEFT OUTER JOIN SENCOM.F0005 F0005 ON LTRIM(ABAC15) = LTRIM(DRKY) AND DRRT IN ('W1','08') AND DRSY NOT IN ('30','32','98')

Even after this its not finding matches between the two tables being joined. The few that are showing up must just be match.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-10 : 16:44:32

is it a hard space of soft space?

try REPLACE(ABAC15,CHAR(160),'')
instead of LTRIM(ABAC15)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcervantes
Starting Member

14 Posts

Posted - 2012-02-13 : 08:25:20

quote:
Originally posted by visakh16

is it a hard space of soft space?

try REPLACE(ABAC15,CHAR(160),'')
instead of LTRIM(ABAC15)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I am not sure how to tell which, I forgot to mention I am pretty new to all of this! :) I tried to use the replace like you mentioned still no luck. It doesn't appear to remove the spaces.

jcervantes
Starting Member

14 Posts

Posted - 2012-02-13 : 09:12:14

quote:
Originally posted by jcervantes

quote:
Originally posted by visakh16

is it a hard space of soft space?

try REPLACE(ABAC15,CHAR(160),'')
instead of LTRIM(ABAC15)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I am not sure how to tell which, I forgot to mention I am pretty new to all of this! :) I tried to use the replace like you mentioned still no luck. It doesn't appear to remove the spaces.

Little more googleing has leads me to believe they are soft spaces.
Here is the HEX info looks like 6 soft spaces (HEX 20).
00000000: 20 20 20 20 20 20 38 37 - 31 30 8710

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 09:33:32

did you try replacing using REPLACE() function?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcervantes
Starting Member

14 Posts

Posted - 2012-02-13 : 15:16:48

quote:
Originally posted by visakh16

did you try replacing using REPLACE() function?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes, I think I am getting even closer, I did a replace replace(F0005.DRKY,CHR(32),'') and that removed the spaces now, my issue is. I have rows i want to filter and on that look blank but must have some other character in them. Is there way to view whats in them so i can replace it or filter on them?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources