Solved

MSSQL 2014 Query Synthax

Posted on 2016-11-04
8
46 Views
Last Modified: 2016-11-11
Hello Experts-

I am reaching out because I am a bit stumped with a query.

QUERY w/ Issue:

SELECT 
			arDate,
			referenceNo,
			custName
			
        FROM (SELECT
                        a.ar_date as [arDate],
						   a.arroNO as [referenceNo],
						   b.bname	as [custName]
							

                FROM AR AS a
                INNER JOIN customer AS b ON a.store_UIN = b.store_UIN 

				 WHERE a.store_UIN = 'FL006' and b.store_UIN = 'FL006'      
				
            ) AS Gdata
   

Open in new window


CUSTOMER TABLE SCHEMA:

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_PRECISION_RADIX	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_CATALOG	CHARACTER_SET_SCHEMA	CHARACTER_SET_NAME	COLLATION_CATALOG	COLLATION_SCHEMA	COLLATION_NAME	DOMAIN_CATALOG	DOMAIN_SCHEMA	DOMAIN_NAME
ROWStage	dbo	Customer	BOXFER	1	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FNAME	2	NULL	YES	nvarchar	40	80	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	LNAME	3	NULL	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	BNAME	4	NULL	YES	nvarchar	61	122	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	ADDRESS	5	NULL	YES	nvarchar	29	58	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	CITY	6	NULL	YES	nvarchar	24	48	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE1	7	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	CUST_NO	8	NULL	YES	nvarchar	11	22	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE2	9	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE3	10	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE4	11	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE5	12	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PHONE6	13	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	STATE	14	NULL	YES	nvarchar	2	4	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	ZIP	15	NULL	YES	nvarchar	10	20	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	CNOTES	16	NULL	YES	nvarchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT1	17	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT2	18	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT3	19	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT4	20	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT5	21	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EXT6	22	NULL	YES	nvarchar	8	16	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC1	23	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC2	24	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC3	25	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC4	26	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC5	27	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	AC6	28	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	SPENT	29	NULL	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NEW_LETTER	30	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	REPEAT_LTR	31	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	VISITS	32	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	OPEN_ACCT	33	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	BILLING	34	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	LABOR	35	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	PARTS	36	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	SUBLET	37	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NONTAXABLE	38	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	LAST_VISIT	39	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	LEVEL	40	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	PO_REQ	41	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FIN_CRG	42	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	EMAIL	43	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	FLEET	44	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	CREDITLIMIT	45	((0))	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FLEET_NO	46	NULL	YES	nvarchar	11	22	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	TAXID	47	('')	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	GSTNONTAXABLE	48	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	GSTTAXID	49	('')	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	TSPENT	50	NULL	YES	float	NULL	NULL	53	2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	TVISITS	51	NULL	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NO_FEE	52	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	PhDisplay	53	NULL	YES	nvarchar	6	12	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	TAXESID	54	NULL	YES	nvarchar	4	8	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	PromptTaxID	55	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FTFleetID	56	NULL	YES	nvarchar	7	14	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	row_GUID	57	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FTNationalAccountID	58	NULL	YES	nvarchar	7	14	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	FleetID	59	NULL	YES	nvarchar	15	30	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	FTNationalAccountNumber	60	NULL	YES	nvarchar	25	50	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EMAIL2	61	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	EMAIL3	62	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	SendEmail1	63	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	SendEmail2	64	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	SendEmail3	65	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	FIRST_VISIT	66	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NationalAccount	67	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	NationalAccountID	68	NULL	YES	nvarchar	11	22	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	MemberID	69	NULL	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	MemberIDSuffix	70	NULL	YES	nvarchar	3	6	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	ClubCode	71	NULL	YES	nvarchar	5	10	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	Customer	Birthday	72	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	Customer	store_UIN	73	NULL	NO	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL

Open in new window


AR TABLE SCHEMA:

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_PRECISION_RADIX	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_CATALOG	CHARACTER_SET_SCHEMA	CHARACTER_SET_NAME	COLLATION_CATALOG	COLLATION_SCHEMA	COLLATION_NAME	DOMAIN_CATALOG	DOMAIN_SCHEMA	DOMAIN_NAME
ROWStage	dbo	AR	BOXFER	1	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	ARCUSTNO	2	NULL	YES	nvarchar	11	22	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	ARRONO	3	NULL	YES	nvarchar	7	14	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	AR_DATE	4	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_AMOUNT	5	NULL	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_DESC	6	NULL	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	AR_BILLING	7	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	AR_PAY_DAY	8	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_PAY_REF	9	NULL	YES	nvarchar	16	32	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	AR_INV_DTE	10	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_SENT	11	((0))	YES	bit	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	LST_INV_DT	12	NULL	YES	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	AR_PAID	13	NULL	YES	money	NULL	NULL	19	10	4	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	PO_NUM	14	NULL	YES	nvarchar	20	40	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	TYPE	15	NULL	YES	nvarchar	1	2	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	TroutID	16	NULL	YES	nvarchar	30	60	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ROWStage	dbo	AR	ARLINK	17	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	row_GUID	18	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	ARPAYMENTLINK	19	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	PAYTYPE_GUID	20	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	PAYTYPERELATION_GUID	21	NULL	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	CREATED	22	(getdate())	NO	datetime2	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ROWStage	dbo	AR	store_UIN	23	NULL	NO	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL

Open in new window



PROBLEM:


When I run the query above I get a result set of 53,712 records. This is incorrect. What I notice is that in the output for referenceNo every record has that first record duplicate, although, the customer name changes.

When I run query
select * from AR

Open in new window

I get 16 records which matches a report I am trying to duplicate.

What I want to do is query the data in the AR table and also query the data in the Customer table do only grab the data from the AR and grab only the name that matches each of those 16 records.

Can you help me figure out where I am incorrect?
0
Comment
Question by:sj77
  • 4
  • 3
8 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41874034
are these 2 tables only linked by field: store_UIN in each table or it need to be linked with more than one field as mentioned?
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41874041
and is it possible to have same store_UIN in table: AR?

if yes, then you probably need to use a Group By clause in your Select SQL statement.
0
 

Author Comment

by:sj77
ID: 41874268
Store_UIN was added in. It basically is the unique ID for each record to its pertinent store. row_GUID was the original unique ID connecting the two tables.

So you say use group by?
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41874280
can you provide some sample data and the current and expected output?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:sj77
ID: 41874340
Ryan of which, the erroneous data or the way its supposed to come out? Cause if I query select * from AR I get the right amount of records.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 41874741
Customer should almost certainly be joined on customer number, probably with store uin as well:

SELECT
                  arDate,
                  referenceNo,
                  custName
                  
        FROM (SELECT
                        a.ar_date as [arDate],
                                       a.arroNO as [referenceNo],
                                       b.bname      as [custName]
                                          

                FROM AR AS a
                INNER JOIN customer AS b ON a.store_UIN = b.store_UIN and a.ARCUSTNO = b.CUST_NO

                         WHERE a.store_UIN = 'FL006' and b.store_UIN = 'FL006'      
                        
            ) AS Gdata
0
 

Author Comment

by:sj77
ID: 41880529
Sorry for the delayed response, I was out of the office. I will test these out today and let you all know. Thank you!
0
 

Author Closing Comment

by:sj77
ID: 41883756
This was the fix to my query, thank you, Scott!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now