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

No! I'm trying to reimplement a set of logging tables we have at work. Currently we have a bunch of background jobs that populate reporting tables from a set of other tables and we want to change that because they are unreliable, slow, and not periodic. I'm trying to investigate other ways of doing this, that is the basis of my question.

It's funny, I see why one would ask if this is for a class. I actually boiled down my problem to a few test cases, and after working on it for some time, I posted those test cases (hence the tables with the phoney data) on this newsgroup.

"Daniel A. Morgan" <damorgan_at_exesolutions.com> wrote in message news:<3CA4E37A.AD9C26D3_at_exesolutions.com>...
> Is this work from a class?
>
> Daniel Morgan
>
>
>
> Steven Garcia wrote:
>
> > 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
> > >
> > >
Received on Sat Mar 30 2002 - 11:10:56 CST