Link to home
Start Free TrialLog in
Avatar of Isaiah Melendez
Isaiah Melendez

asked on

MSSQL 2014 Query Synthax

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?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image
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?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image
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.
Avatar of Isaiah Melendez
Isaiah Melendez

ASKER

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?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image
can you provide some sample data and the current and expected output?
Avatar of Isaiah Melendez
Isaiah Melendez

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Isaiah Melendez
Isaiah Melendez

ASKER

Sorry for the delayed response, I was out of the office. I will test these out today and let you all know. Thank you!
Avatar of Isaiah Melendez
Isaiah Melendez

ASKER

This was the fix to my query, thank you, Scott!