How would I create this T-SQL subquery?

Question

I am losing my touch here. In the old days I would come up with a super T-SQL query,

Select t1.Number, t1.TransactionType, t1.Description, t1.Vendor,
(Select max(t2.BatchId) From table2 t2
 Where t1.Number=t2.Number and t1.TransactionType=t2.TransactionType
 Group By t2.number,t2.transactiontype) As BatchId
From table1 t1

I need a second column from table2. Column is called "Result".

Example:

table1:
Number, TransactionType, Description, Vendor
1, Type1, Test1, Vendor1
2, Type1, Test2, Vendor2
1, Type2, Test3, Vendor3
3, Type2, Test1, Vendor2

table2:
Number, TransactionType, BatchId, Result
1, Type1, 12, error1
1, Type1, 4, error2
1, Type2, 8, success
3, Type2, 7, success

wanted ResultSet:
Number, TransactionType, Description, Vendor, BatchId, Result
1, Type1, Test1, Vendor1, 12, error2
2, Type1, Test2, Vendor2, null, null
1, Type2, Test3, Vendor3, 8,success
3, Type2, Test1, Vendor2, 7,success

The posted query takes care of the first 5 columns. Now how about that last column?

Add the required table column to your sub-query SELECT statement. IE: (Select max(t2.BatchId), Result From table2 t2 ... — blearn, Jun 27 '12 at 17:07
Subqueries may not return more than one column in this context. — Pittsburgh DBA, Jun 27 '12 at 17:51

oryol · Accepted Answer · 2012-06-27T23:26:17.887

0

select t1.Number, t1.TransactionType, t1.Description, t1.Vendor, t2.BatchId, t2.Result
from table1 t1
left join
(
  select t2.TransactionType, t2.number, t2.Result, t2.BatchId,
    row_number() over (partition by t2.number, t2.TransactionType order by t2.BatchId desc) as BatchNumber
  from table2 t2
) t2 on t1.Number = t2.Number
  and t1.TransactionType = t2.TransactionType
  and t2.BatchNumber = 1

If you can be sure that for each row from t1 you have related rows in t2 then it will be better to replace left join with inner join.

UPD There was an error which was correctly noticed in the comments. I've changed the query to the right version.

edited Jun 27 '12 at 23:26

answered Jun 27 '12 at 17:12

oryol

5,178
2
23
18

2

You have converted the left join to an inner join with your syntax. add t.batchnumber = 1 to the ON clause not the where clause. http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN – HLGEM Jun 27 '12 at 18:04
That did the trick oryol. And no I have no guarantee that a record has been transmitted. So the result table might not have corresponding values. The left join is perfect. – Paul Hilt Jun 27 '12 at 19:37

How would I create this T-SQL subquery?

1 Answers1