Help with join between tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with join between tables

Author

Topic

jmarendo
Starting Member

6 Posts

Posted - 2010-01-15 : 10:43:28

Hello,

I'm working with two databases located on the same server. The databases are ([DatabaseName].[Owner].[TableName]):

PaymentRecData.PaymentRec.SalesTransactions
SalesData.pos.AuthTransaction

Each table contains transactions for a given date (in this case, I'm only interested in the ones dated 2009-12-20). The AuthTransaction table contains 397930 records and the SalesTransactions table contains 393966 records for that date. I need to join both tables to find the specific records existing in the AuthTransaction table but missing in the SalesTransactions table.

Here's the thing, though: Each table contains data that is considered the same from a business perspective but the fields in question are stored as different data types in each table (don't ask me why--I didn't design it). So, basically, I'm interested in four fields that exist in each table. In the AuthTransaction table they are:

BusinessDayDate (char(10))
RetailStoreID (char(5))
WorkstationID (char(3))
TransactionSequenceNumber (int)

In the SalesTransactions table they are:

SalesDate (datetime)
Location (smallint)
TermID (smallint)
TranNum (int)

SQL isn't a strong suit of mine so bear with me. This is what I've tried so far (focusing on a single store for now):

select retailStoreId,workstationId,transactionSequenceyNumber,businessDayDate
from SalesData.pos.AuthTransaction
left outer join PaymentRecData.PaymentRec.SalesTransactions
on businessDayDate = salesDate
where businessDayDate = '2009-12-20'
and retailStoreId = '00001'
and tranNum is null
order by retailStoreId,workstationId,transactionSequenceyNumber;

The query runs but doesn't work: execution time is approximately 6 minutes and no results are returned. My concern is the different data types being used and the formation of my T-SQL, obviously. Any ideas?

Jeff

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 10:47:24

What is the joining criteria between the two tables? In your example you have used only the dates?? Does not make much sense..

Can you provide some sample data from both tables and let us know what your expected output will be?

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 11:05:56

This perhaps?


SELECT	retailStoreId,workstationId, transactionSequenceyNumber, businessDayDate
FROM	SalesData.pos.AuthTransaction
	LEFT OUTER JOIN PaymentRecData.PaymentRec.SalesTransactions
		 ON salesDate = CONVERT(datetime, businessDayDate)	-- **1
		AND Location = CONVERT(smallint, RetailStoreID)
		AND TermID = CONVERT(smallint, WorkstationID)
		AND TranNum = TransactionSequenceNumber
WHERE	    businessDayDate = '2009-12-20'
	AND retailStoreId = '00001'
	AND tranNum IS NULL
ORDER BY retailStoreId,workstationId,transactionSequenceyNumber

Note **1 : The conversion of a date in to the format '2009-12-20' (in your businessDayDate column) may not implicitly convert to a datetime without specifying a format-type, or some string manipulation. It depends on the locale settings of the server [which SQL is installed on]. SO even if it works, if you deploy it to another server it may break.

I think

ON salesDate = CONVERT(datetime, businessDayDate, 121)

would be OK to convert 'yyyy-mm-dd' to datetime, but I haven't tested it.

jmarendo
Starting Member

6 Posts

Posted - 2010-01-15 : 11:07:24

Yes, I'm not surprised the query doesn't make sense (particulary the criteria used for "on.."). Sample data for a single transaction that exists in both tables is below. I should point out that there's no technical relationship between the tables (that is, no foreign keys between the two) and the primary keys for each table are different as well.

In AuthTransaction:

BusinessDayDate (PK, char(10), not null)
RetailStoreID (PK, char(5), not null)
WorkstationID (PK, char(3), not null)
TransactionSequenceNumber (PK, int, not null)
LineItemSequenceNumber (PK, smallint, not null)

In SalesTransactions:

RowKey (PK, uniqueidentifier, not null)
SalesDate (PK, datetime, not null)
Location (smallint, not null)
TermID (smallint, null)
TranNum (int, null)

The formatting is a little skewed, but here's some example data from AuthTransaction:


RetailStoreID		WorkstationID		TransactionSequenceNumber		BusinessDayDate		LineItemSequenceNumber
00001			001			368					2009-12-20		11

Here's some example data from SalesTransactions:


Location		TermID			TranNum					SalesDate		RowKey
1			1			368					2009-12-20 00:00:00.000	A4F78ACD-31EE-DE11-AA76-001708500056

jmarendo
Starting Member

6 Posts

Posted - 2010-01-15 : 11:19:29

Kristen,

That query is much better than what I had. It returns quickly but has 0 results. The counts are different for the queries below so whatever query is used should show me the difference, ultimately.

For example:

-- 453 records returned
retailStoreId,workstationId,transactionSequenceyNumber,businessDayDate
from SalesData.pos.AuthTransaction
where businessDayDate = '2009-12-20'
and retailStoreId = '00001'
order by workstationId,transactionSequenceyNumber;

-- 451 records returned
select location,termId,tranNum,salesDate
from PaymentRecData.PaymentRec.SalesTransactions
where salesDate = '2009-12-20'
and location = 1
order by termId,tranNum;

I'd like to find the 2 records from AuthTransaction that aren't in the SalesTransactions table. Does that make sense?

Jeff

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 11:30:37

I guess the query Kristen has provided should give you those results...unless..there is a possibility that you have more than one row in AuthTransaction for the same RetailStoreID,WorkstationID,TransactionSequenceNumber and BusinessDayDate....just with a different LineItemSequenceNumber

Based on your PK definition..it is possible

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 11:37:40

May be not...

Try this..

SELECT	RetailStoreID,WorkstationID, TransactionSequenceNumber, BusinessDayDate
FROM	@t
	LEFT OUTER JOIN @r
		 ON SalesDate = CONVERT(datetime, BusinessDayDate)	-- **1
		AND Location = CONVERT(smallint, RetailStoreID)
		AND TermID = CONVERT(smallint, WorkstationID)
		AND TranNum = TransactionSequenceNumber
		and BusinessDayDate = '2009-12-20'
	AND RetailStoreID = '00001'
WHERE	TranNum IS NULL

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 11:41:49

EDIT: Ignore this, I have gone less-specific, rather than more-specific!

Right, lets just try with SalesDate and Location then. I've restricted to 100 rows Max in case it mis-fires!

SELECT TOP 100
retailStoreId,workstationId, transactionSequenceyNumber, businessDayDate
FROM SalesData.pos.AuthTransaction
LEFT OUTER JOIN PaymentRecData.PaymentRec.SalesTransactions
ON salesDate = CONVERT(datetime, businessDayDate)
AND Location = CONVERT(smallint, RetailStoreID)
-- AND TermID = CONVERT(smallint, WorkstationID)
-- AND TranNum = TransactionSequenceNumber
WHERE businessDayDate = '2009-12-20'
AND retailStoreId = '00001'
AND tranNum IS NULL
ORDER BY retailStoreId,workstationId,transactionSequenceyNumber

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 11:49:03

Lets try MATCHING some rows to start with, to make sure that the matching is actually working:


SELECT TOP 100
	retailStoreId, 
	businessDayDate, salesDate,	-- Do they look the same?
	Location, RetailStoreID,	-- ... and data for these two??
	TermID, WorkstationID,		-- ... etc
	TranNum, TransactionSequenceNumber
FROM	SalesData.pos.AuthTransaction
	JOIN PaymentRecData.PaymentRec.SalesTransactions
		 ON salesDate = CONVERT(datetime, businessDayDate)
		AND Location = CONVERT(smallint, RetailStoreID)
		AND TermID = CONVERT(smallint, WorkstationID)	-- (2)
		AND TranNum = TransactionSequenceNumber		-- (1)
WHERE	    businessDayDate = '2009-12-20'
	AND retailStoreId = '00001'
ORDER BY retailStoreId,workstationId,transactionSequenceyNumber

If you don't get any rows remove line (1) and line (2) if you still don't get any

If you get rows do they look matched (based on other columns)

jmarendo
Starting Member

6 Posts

Posted - 2010-01-15 : 13:35:45

Vijay,

The query ran but brought back rows for multiple stores and business dates.

Kristen,

The query wouldn't run (message 209, state 1, line 1, ambiguous column name 'retailStoreId').

I'm looking at both queries to see what I can do with them.

jmarendo
Starting Member

6 Posts

Posted - 2010-01-15 : 13:41:05

Kristen,

Changed the query to qualify the RetailStoreID column:

select top 100 a.retailStoreId
...
from SalesData.posAuthTransaction a
...
order by a.retailStoreId,

It returned results that appear correct at first glance. I need to go through them more closely and make sure.

Jeff

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 13:54:54

This is some sample data I built based on your example you've shown...the query seems to work for me...Anything I'm missing here.

declare @t table 
(RetailStoreID varchar(20),WorkstationID varchar(20),TransactionSequenceNumber int,BusinessDayDate varchar(10),LineItemSequenceNumber int)
insert @t
select '00001','001',368,'2009-12-20',11
union all select '00002','002',368,'2009-12-20',11

declare @r table
(Location int,TermID int,TranNum int, SalesDate datetime,RowKey varchar(100))
insert @r
select 1,1,368,'2009-12-20','A4F78ACD-31EE-DE11-AA76-001708500056'

Query

SELECT	RetailStoreID,WorkstationID, TransactionSequenceNumber, BusinessDayDate
FROM	@t
	LEFT OUTER JOIN @r
		 ON SalesDate = CONVERT(datetime, BusinessDayDate)	-- **1
		AND Location = CONVERT(smallint, RetailStoreID)
		AND TermID = CONVERT(smallint, WorkstationID)
		AND TranNum = TransactionSequenceNumber
and BusinessDayDate = '2009-12-20'	AND RetailStoreID = '00001'
WHERE	TranNum IS NULL

Result

RetailStoreID        WorkstationID        TransactionSequenceNumber BusinessDayDate
-------------------- -------------------- ------------------------- ---------------
00002                002                  368                       2009-12-20

jmarendo
Starting Member

6 Posts

Posted - 2010-01-15 : 14:24:19

I'll be offline for a bit. I tweaked the last query Kristen posted and it ran--successfully I think. It returned 453 rows, which is the correct count of records in the AuthTransaction table for store 1 with business date 2009-12-20. The SalesTransactions table has 451 rows meeting the same criteria so I'm a little confused why 453 rows were returned with Kristen's query (I would expect only 451 rows returned). Of course I need to review more.

I will work with the last one Vijay posted and see how that goes.

Also, one thing I'll do before replying next is to identify the missing transactions for store 1 with the 2009-12-20 business date. That is, I want to confirm the "missing" transactions are truly missing. I expect to find 2 missing records (that is, 2 records present in the AuthTransaction table that are not in the SalesTransactions table). That should help make things more clear and will help in providing info for test purposes hereafter.

Thank you both for helping out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources