Joins - 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 2000 Forums

SQL Server Development (2000)

Joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-12 : 08:40:52

Alexander writes "Hi,

I have two tables that I am trying to join. Sounds simple, but it is not. Here is my problem; The join works find accept when the left table has two records with the same date and amount (part of join criteria) and the right table has only one record with the same date and amount.

T1
UniqueID  Account    Date     Amount
1         11111      11/3/01  30.00 
2         33333      11/4/01  40.00 
3         11111      11/3/01  30.00

T2
Account  Date     Amount
11111    11/3/01  30.00

The results of the join will produce

T1.Account    T1.Date   T1.Amount     T2.Amount
11111         11/3/01   30.00         30.00
33333         11/4/01   40.00         NULL
11111         11/3/01   30.00         30.00

As you can see account 11111 appears 2 times in the results which is correct. However, the amount should only appear once because it is only once in T2. Account 33333 is also correct.
The bottom line is that I want Account 11111 to appear in the results, but because it is only once in T2 it should appear as NULL like the listing below:

T1.Account    T1.Date   T1.Amount     T2.Amount
11111         11/3/01   30.00         30.00
33333         11/4/01   40.00         NULL
11111         11/3/01   30.00         NULL

Please help.

Thanks."

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2001-12-12 : 08:54:45

In short my view is you can't do this. Your data model is under speced. The problem is identifying which 111111 record in T1 to join to T2. Unless T1 has some other information (common in structure to T2) which allows a specific version of 111111 to be joined to a specific version of a record in T2, you're a goner.

This is probably highlighting a weakness in your data model....sort that out first, and then the problem will go away.

mono
Starting Member

36 Posts

Posted - 2001-12-12 : 09:21:24

In the result set you want to records differ only in the T2.Amount field so T2 must be part of the primary key if the records are to be unique (they should always be unique). But since it can be NULL, it can't be part of a PK.
If you had 3 or more T1 records matching the T2 record then you would end up with 2 rows that were identical and lose the ability to tell individual transactions apart.

That said, something like this will do what you want.


select T1.uniqueID, T1.account, T1.Date, T1.Amount, 
  (case T1.uniqueID = (select min(uniqueID) 
                       from T1 AS firstTrans 
                       where T1.Date = firstTrans.Date and
                             T1.account = firstTrans.account and 
                             T1.amount = firstTrans.amount) 
     then T2.Amount 
     else NULL end) AS Amount
from T1 left join T2 
on T1.Date = T2.Date and T1.account  = T2.account and T1.amount = T2.amount

mono

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources