Re: Merge result of two queries?

On Sun, 8 Jan 2006 00:09:56 -0000, "Edward" <egoduk_at_NOSPAM_hotmail.com> wrote:

>I need to 'fill in the gaps' in the results of SQL1 - notice that it
>doesn't
>return any results for 06/01/2006.
>SQL2 returns results for all dates, with a forced count of 0.
>
>How do I construct a query that would pad gaps in SQL1's results with the
>results from SQL2?

 When you're talking about filling gaps, the first thing to think of is an outer join.

>Producing results of:
>08/01/2006 20
>07/01/2006 33
>06/01/2006 0
>05/01/2006 22
>04/01/2006 19
>
>Thanks in advance,
>
>SQL1:
>select date, count from table;
>Results:
>08/01/2006 20
>07/01/2006 33
>05/01/2006 22
>04/01/2006 19
>
>SQL2:
>select date, 0 as count from dateTable;
>Results:
>08/01/2006 0
>07/01/2006 0
>06/01/2006 0
>05/01/2006 0
>04/01/2006 0

select dateTable.date, nvl(table.count, 0) from dateTable
left outer join table
on (dt.date = t.date)

 (Presumably these aren't the real column or table names since it uses several SQL keywords [count, date, table], so substitute where necessary)

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool