joining 3 tables with data from each to be returned
need help to capture records from 3 different tables, based on 3 different dates. ANY transaction from any of the tables should be included and results combined where possible: Sample data
Table 1 sales table Item#, Sold Date, invoice, etc. 111, 1/2/14, poabc 222, 1/3/14, poedf 123, 1/4/14, poxyz Table 2 process table Item#, proc Date, paid amt, etc. 111, 1/12/14, 12 456, 1/25/14, 16 Table 3 canceled table Item#, cancel date, reason, etc. 222, 1/8/14, reason1 555, 1/9/14, reason2
Results should include any item sold, processed or canceled within a date range, one row for each – Example RESULTS Item#, sold date, proc date, cancelled date, reason, etc.
111, 1/2/14, 1/12/14, (null) , (null) , 222, 1/3/14, (null) , 1/8/14, reason1 123, 1/4/14, (null) , (null) , (null) , 456, (null), 1/25/14, (null) , (null) , 555, (null) , (null) ,1/9/14, reason2
Not sure how to approach, union, joins, etc. Tried a view, based on item#s, as Left outer on proc = sold(+) and proc = cancelled(+), and then unioned it with a 2nd iew of the cancelled items numbers not in the above, left outer cancelled = proc(+) and cancelled = sold(+) and (proc IS NULL and sold IS NULL) then what? Not sure this is the best approach or how else to set this up
You can do this with a full outer join, which most databases support.
select coalesce(s.item, p.item, ) as item, s.SoldDate, p.procDate, c.cancelledDate, c.reason from sales s full outer join process p on p.item = s.item full outer join cancelled c on c.item = coalesce(s.item, c.item);