Re: Help with SQL query (and outer joins...)

I actually think a better example would be the following. I have two tables...

Table 1:                                        Table 2:
Logday    ID    PageHits               Logday    ID    UserHits
--------   ----  ----------              --------   ----   ---------
20-02      400  10                         20-02     400   5
20-02      500  5                           20-02     600   5
21-02      400  5                           21-02     500   10
21-02      300  15                         21-02     300   10

I want to create a view that contains the "union" of both these tables..so the resultset would look like

Logday ID PageHits UserHits

-----------  ----   ----------  ----------
20-02         400   10             5
20-02         500   5               0
20-02         600   0               5
21-02         400   5               0
21-02         500   0               10
21-02         300   15             10

Notice in the columns where no data is present in either one of the parent tables, a "0" is supplied. A few things...

1. Table 1 and Table 2 are guaranteed to contain complete rows (not "20-02 400 0").
2. It's OK for the resulting table to have rows where there is 0 in both the PageHits and UserHits column, or if the resulting table doesn't contain those rows.

I'm having trouble formulating a view (sql query) for this. Any help?

Thanks, Steve

"Steven Garcia" <sgarcia_at_inktomi.com> wrote in message news:3ca4e358$1_at_digink...
> I'm having trouble with a view I'm developing. For the simplified
> version...I have two views that look like:
>
> View 1:
> Logday (date, 'ddd') totalcount
> --------------------- -----------
> 20-FEB-2002 10
> 21-FEB-2002 20
>
> View 2:
> Logday (date, 'ddd') revenue
> --------------------- ----------
> 21-FEB-2002 5.00
> 22-FEB-2002 8.00
>
> I want to create an uber view from both of these views that it will look
> like
> Logday (date, 'ddd') totalcount revenue
> --------------------- ---------- --------
> 20-FEB-2002 10 0
> 21-FEB-2002 20 5.00
> 22-FEB-2002 0 8.00
>
> I know this is a perfect candidate for an outer join. The real example
I'm
> trying to do is more complicated because there are 4 views (each with a
> logday column) and I want to create the uber view that has a column from
> each of the 4 views, along with some other data that will exist across all
> four views (so there won't be a need to do a outer-join on those columns.)
>
> How do I construct such a query? I'm trying to solve my problem in such a
> way that my uber view will look like
> the following...
>
> Logday col1 col2 col3
> col4
> ------------------- ---------- ---------- ---------- ----------

--


> 20-FEB-2002           0                1                 15

0


> 21-FEB-2002           20              10               0                23

> 22-FEB-2002           14               0                14               0

> 23-FEB-2002            0                4                0

> 34

>

> where I have 0 in the columns where the data doesn't exist from my

"feeder"


> views.

>

> We are currently solving this problem by having a job on a daily basis do

> this population in a table, but our business requirements have changed

such


> that the current implementation is not suitable.  I'm a little worried

about


> performance of this view but these reporting tables are not access often

in


> our application (so a performance hit is something that I will accept.)

>

> Thanks for your help,

> Steve Garcia

>

>