Solved

MSSQL 2014 Query Synthax

Posted on 2016-11-04
8
36 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
can you provide some sample data and the current and expected output?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:sj77
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
This was the fix to my query, thank you, Scott!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

771 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

10 Experts available now in Live!

Get 1:1 Help Now