Ordering fields by datetime from two tables

Question

I have two tables (IncomingDocs) and (OutgoingDocs) each have a field called SubmitDate (datatype Datetime) and DepositNo (datatype Int) Each DeposotNo may have multiple Submitdate in a single table.

    For example;



_IncomingDocs table_

**DepositNo**  |  **SubmitDate**  

    1  |  10/3/2012 12:00:00 AM  
    1  |  12/3/2012 12:00:00 AM  
    2  |  10/3/2012 12:00:00 AM  
    3  |  10/3/2012 12:00:00 AM  
    3  |  13/3/2012 12:00:00 AM  
    4  |  20/3/2012 12:00:00 AM  

_OutgoingDocs table_

**DepositNo**  |  **SubmitDate**  

    1  |  11/3/2012 12:00:00 AM  
    2  |  15/3/2012 12:00:00 AM  
    3  |  12/3/2012 12:00:00 AM  
    3  |  14/3/2012 12:00:00 AM  
    4  |  19/3/2012 12:00:00 AM  

    SELECT DepositNo, SubmitDate  
    FROM IncomingDocs AS i, OutgoingDocs as o  
    WHERE DepositNo = 1  
    ORDER BY SubmitDate  

* The output of the query should be:

_Output table_

**DepositNo**  |  **SubmitDate**  
1  |  10/3/2012 12:00:00 AM  << From IncomingDocs table  
1  |  11/3/2012 12:00:00 AM  << From OutgoingDocs table  
1  |  12/3/2012 12:00:00 AM  << From IncomingDocs table

I want the output to be records of a specific DepositNo (specified in the WHERE clause) and ordered by the Submitdate.

score 3 · Answer 1 · edited Nov 25 '12 at 14:58

3

I am not sure if sql server supports union, but something like this

Select DepositNo, SubmitDate from IncomingDocs where DepositNo = X
UNION
Select DepositNo, SubmitDate from OutgoingDocs where DepositNo = X
ORDER BY SubmitDate

should probably work.

edited Nov 25 '12 at 14:58

Aaron Bertrand

272,866
37
466
490

answered Nov 25 '12 at 09:57

nvlass

665
1
6
15

Thanks. It does work, but the UNION ALL works better in my case! – Arwa A Nov 27 '12 at 09:11

score 3 · Accepted Answer · edited Nov 25 '12 at 11:13

3

-- you'll need 'UNION ALL' if you need to keep both rows from each table that -- happen to be duplicate

SELECT DepositNo, SubmitDate  
FROM (  
     SELECT DepositNo, SubmitDate  
     FROM OutgoingDocs  
     WHERE DepositNo = 1 --Or a variable @MyDepositNo  
     UNION ALL --this will preserve duplicate rows if there are any between the two tables  
     SELECT DepositNo, SubmitDate  
     FROM IncomingDocs  
     WHERE DepositNo = 1 --as above  
) TT  
ORDER BY TT.SubmitDate

edited Nov 25 '12 at 11:13

ypercubeᵀᴹ

113,259
19
174
235

answered Nov 25 '12 at 10:54

JM Hicks

1,282
1
11
22

You don't need a subquery for this. Just remove the parentheses and the first `SELECT ... FROM` – ypercubeᵀᴹ Nov 25 '12 at 11:14

Ordering fields by datetime from two tables

2 Answers2