Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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...
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
> ------------------- ---------- ---------- ---------- ----------
--Received on Fri Mar 29 2002 - 20:35:47 CST
> 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
>
>