SQL Query

I got help from an expert with a query that provides me the Parent Customer number even if  a child account performed the transaction. It works! However, when I add the query into an existing query to provide the columns, it's showing the same invoice number repeatedly, no matter if the customer number is correct or not. The query is called SalesHistory1, and it is attached. the part highlighted in yellow and red is what was added.

Attached is also example of the results
SalesHistory1.docx
SalesHistory1-Sample-Results.png
LVL 1
isamesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

isamesAuthor Commented:
Ok.
0
Brian CroweDatabase AdministratorCommented:
I believe you have a join issue which is easy to do when you use such antiquated joining syntax.  You appear to be cross joining the second ARCF reference.

FROM RSID
INNER JOIN RSIH
	ON RSID.RSIH_INVOICE_N = RSIH.RSIH_INVOICE_N
INNER JOIN RSPF
	ON RSID.RSID_PROD_N = RSPF.RSPF_PROD_N
INNER JOIN ARCF
	ON ARCF.ARCF_CUST_N = RSIH.RSIH_CUST_N
INNER JOIN RSPC
	ON RSPC.RSPC_CLASS = RSPF.RSPF_CLASS
INNER JOIN ARCF AS A
	ON ???
INNER JOIN rsih AS R
	ON R.rsih_cust_n = A.arcf_cust_n
WHERE RSIH.RSIH_DOCUMENT_CREATE_DATE >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()) - 731, 0)

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Your query is complex so you should write it in a more simple way to be more readable. I did some changes and came with the same question as Brian's. You aren't joining ARCF (alias A) and that's multiplying your results.
Here is your query with my changes (moved the joins in the WHERE clause to INNER JOIN and removed two subselects adding those tables to the FROM clause):
SELECT 
	LTRIM(RTRIM(RSID.RSIH_INVOICE_N)) as INVOICE_N,
	RSID.RSID_SEQ_N, RSIH.RSIH_DATE,
		CASE 
			WHEN a.arcf_parent_cust_n = ' ' THEN a.arcf_cust_n 
			ELSE a.arcf_parent_cust_n 
		END AS Customer_Number,
		CASE 
			WHEN a.arcf_parent_cust_n = ' ' THEN ' ' 
			ELSE a.arcf_cust_n 
		END AS Sub_Account,
		CASE 
			WHEN a.arcf_parent_cust_n = ' ' THEN a.arcf_name 
			ELSE
				(SELECT a_parent.arcf_name
				 FROM arcf a_parent 
				 WHERE a_parent.arcf_cust_n = a.arcf_parent_cust_n) END AS Parent_Name,	    
	RSIH.RSIH_BILL_NAME,
	GLDV.GLDV_ALT_DIV_N AS STORE_NUM,
	LTRIM(RTRIM(RSID.RSID_PROD_N)) AS PROD_NUM,
	RSID.RSID_DESC,
	RSID.RSID_TYPE,
	RSID.RSID_QTY,
	(RSID.RSID_PROD_COST*RSID.RSID_QTY) AS PRODUCTCOST,
	RSID.RSID_EXTENDED,
	RSID.RSID_DISC_AMT,
	RSID.RSID_UNIT,
	ARCF.ARCF_C_RATING,
	ARCF.ARCF_PHONE,
	ARCF.ARCF_OPENED,
	ARCF.ARCF_ADDR1,
	ARCF.ARCF_ADDR2,
	ARCF.ARCF_CITY,
	ARCF.ARCF_PROVINCE,
	ARCF.ARCF_POSTAL,
	RSIH.RSIH_DISC_PROG_CODE,
	ARCF.ARCF_SALESMAN,
	CASE 
		WHEN (ARCF.ARCF_CUST_N IN (SELECT ARIC.ARCF_CUST_N FROM ARIC)) THEN 
				(SELECT ARIC.ARCF_SIC_CODE FROM ARIC WHERE ARIC.ARCF_CUST_N = ARCF.ARCF_CUST_N AND ARIC.ARCF_SIC_CODE LIKE 'V%')
		ELSE '' 
	END AS VAN_CODE,
	'' AS FBH_CODE,
	RSPF.RSPF_VENDOR_N,
	APVF.APVF_NAME AS VENDOR_NAME,
	'' AS FR_REVENUE_TYPE,
	'' as ACCT_GRPING_NUM,
	LTRIM(RTRIM(RSPF.RSPF_CLASS)) AS CLASSNBR,
	RSPC.RSPC_DESC AS CLASS_DESC,
	LTRIM(RTRIM(RSPF.RSPF_GROUP_N)) AS GROUPNBR,
	RSIH.RSIH_ON_ACCT AS ON_ACCOUNT,
	RSIH.RSIH_DOCUMENT_CREATE_DATE,
	RSIH.RSIH_TAX_AMT
FROM RSID,
	 ARCF A  '-- What should be the joining clause for this table?
	INNER JOIN RSIH ON RSIH.RSIH_INVOICE_N = RSID.RSIH_INVOICE_N
	INNER JOIN RSPF ON RSPF.RSPF_PROD_N = RSID.RSID_PROD_N
	INNER JOIN ARCF ON ARCF.ARCF_CUST_N = RSIH.RSIH_CUST_N
	INNER JOIN RSPC ON RSPC.RSPC_CLASS=RSPF.RSPF_CLASS
	INNER JOIN rsih r ON r.rsih_cust_n=a.arcf_cust_n
	INNER JOIN APVF ON APVF_VENDOR_N = RSPF.RSPF_VENDOR_N
	INNER JOIN GLDV ON GLDV.GLDV_DIV = RSID.RSID_DIV
WHERE RSIH.RSIH_DOCUMENT_CREATE_DATE >= DATEADD(Day, DATEDIFF(Day, 0, GETDATE()) - 731, 0)

Open in new window

NOTE: I didn't change more because I don't know more about your data model.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.