asked on

SQL - Rewrite statement

Below is a portion of my code in a SQL Select Statement. The problem I have is with

INNER JOIN SYS_AddressFormat AF ON AF.id_addressFormat = CO.id_format

Unfortunately, some data results with nothing being returned. CO.id_format = 0 and AF.id_addressFormat must be >= 0.

Is there a way to rewrite this so it will return data if even if the data does not match below.

INNER JOIN SYS_AddressFormat AF ON AF.id_addressFormat = CO.id_format

Want to include when it exists but exclude when it doesn't.

ISNULL(STUFF((
	SELECT '<CUSTOMER_Address> 
			<CO.rowid="' + cast(CO.rowid as varchar(5)) + '"/> 
			<CO.id_format="' + cast(CO.id_format as varchar(5)) + '"/> 
			<id_address name="' + cast(id_address as varchar(5)) + '"/> 
			<Name name="' + Name + '"/> 
			<CompanyName name="' + companyName + '"/>
			<ck_shippingAdd name="' + cast(ck_shippingAdd as varchar(1)) + '"/>
			<ck_invoiceAdd name="' + cast(ck_invoiceAdd as varchar(1)) + '"/>
			<attn name="' + attn + '"/>
			<streetAddress name="' + streetAddress + '"/>
			<extraNote name="' + extraNote + '"/>
			<state name="' + state + '"/>
			<zipCode name="' + zipCode + '"/>
			<city name="' + city + '"/>
			<country name="' + CO.en + '"/>
			<telephone name="' + telephone + '"/>
		</CUSTOMER_Address>' 
	FROM CUSTOMER_Address CA2 
	INNER JOIN BASE_Country CO ON CO.rowid = CA2.id_country 
	INNER JOIN SYS_AddressFormat AF ON AF.id_addressFormat = CO.id_format 
	WHERE CA2.id_customer = C.CustomerId 
	AND CA2.iSActive = 1 
	ORDER BY CA2.Name for xml path('a'), type).value('.','nvarchar(max)'),1,0,''),'') addresses

Open in new window

Jason Kania

If I understand you correctly, you would want to use a LEFT JOIN instead of an INNER JOIN so

INNER JOIN SYS_AddressFormat AF ON AF.id_addressFormat = CO.id_format

would become

LEFT JOIN SYS_AddressFormat AF ON AF.id_addressFormat = CO.id_format

If this doesn't give you what you are looking for, please add more detail to your question.

CipherIS

ASKER

Not working. I tried LEFT and RIGHT. Field is empty.

Below is entire SQL Statement It is the LEFT JOIN SYS_AddressFormat AF ON AF.id_addressFormat = CO.id_format part of the code below. If I remove this part of code and references to it, it returns the expected result.

SELECT DISTINCT 
	C.CustomerId, 
	C.CustomerId as id_customer, 
	C.Name as Name, 
	ISNULL(STUFF((
			SELECT '<CUSTOMER_Address> 	
						<id_address name="'+cast(id_address as varchar(5))+'"/> 	
						<Name name="'+Name+'"/> 	
						<CompanyName name="'+companyName+'"/> 	
						<ck_shippingAdd name="'+cast(ck_shippingAdd as varchar(1))+'"/> 	
						<ck_invoiceAdd name="'+cast(ck_invoiceAdd as varchar(1))+'"/> 	
						<attn name="'+attn+'"/> 	
						<streetAddress name="'+streetAddress+'"/> 	
						<extraNote name="'+extraNote+'"/> 	
						<state name="'+state+'"/> 	
						<zipCode name="'+zipCode+'"/> 	
						<city name="'+city+'"/> 	
						<country name="'+CO.en+'"/> 	
						<telephone name="'+telephone+'"/>    
						<fullAddress name="'+
							Replace( 
								Replace( 
									Replace( 
										Replace(  
											Replace(  
												Replace(AF.format, '<CompanyName>', companyName),  
												'<StreetAddress>', streetAddress+CASE WHEN extraNote <> '' THEN Char(10)+extraNote ELSE '' END), 
												'<State>', state), 
												'<ZipCode>', zipCode), 
												'<City>', city), 
												'<Country>', CO.en)+'"/> 
					</CUSTOMER_Address>' 
					FROM CUSTOMER_Address CA2 
					INNER JOIN BASE_Country CO ON CO.rowid = CA2.id_country 
					LEFT JOIN SYS_AddressFormat AF ON AF.id_addressFormat = CO.id_format 
					WHERE CA2.id_customer = C.CustomerId 
					AND CA2.iSActive = 1 
					order by CA2.Name for xml path('a'), type).value('.','nvarchar(max)'),1,0,''),'')  addresses 
FROM BASE_Customer C 
WHERE C.isActive = 1 
AND [C].[distributor] = 0 
AND c.CustomerId = 963
ORDER BY C.Name

Open in new window

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CipherIS

ASKER

Thank You. That resolved the issue.