Question on Views/Outer Joins for SQL Gurus.....

Question on Views/Outer Joins for SQL Gurus.....

Post by RamMohan Madapati 896-449 » Tue, 26 Sep 1995 04:00:00



I am having a problem with generating sql statement(s) using
outer joins for views on a set of tables, the scenario is
described below:

(Notation:
        Cap Letter with underlining: Table Name
        -> (horizontal/vertical indiates relaion
        LHS of relation M:Nt means: m to n rows of table t
                can be related with:
        RHS of relation M:Nt means: m to n rows of table t
        0:N means null relation is possible
)

  U              T           E
-----          ------      -----

    1:1u->1:Mt       1:1t->0:Me

                1:1t

                  |
                  V

                0:1r

                  R            P           G
                -----        -----       -----

                    1:Mr->1:1p    1:Mp->1:1g

Apparently, the above situation is forcing me to use at least
two outer joins (since the data is needed even on null
relation), one between T->E and another between T->R.

The sql server is * on sql statement to join these
different tables in one or more levels of views.
The join is expected to select columns from all but table 'R'.

I would sincerely appreciate any suggestions/solutions.

-thanks.

 
 
 

1. Question for a guru -?- regarding outer join+hints...

Hi here,
I have 2 tables: table_A (which is pretty large, millions of records),
and              table_B (about 40 records).
I've noticed the following (remember I use no index for this query):

This query:

is much quicker and more efficient than:

that yet looks simpler. Of course both queries give me the same result;
I checked Oracle's behaviour with the EXPLAIN PLAN statement:

and I found:

with the first query, and:

with the 2nd one (the latter is then obviously heavier, although the
code is lighter).

Could anyone please explain me WHY the first query is the best one ??

Thanks !

Spendius

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. Volunteer Access developer for brief project in Silicon Valley

3. Record locking differences between FPW2.6 and VFP5

4. SQL-92 outer join vs T-SQL outer join (6.5 or 7.0) - test script included

5. Database access right

6. Illegal Outer Join Error - SQL Guru Needed

7. MS SQL Outer Joins VS Sybase Outer Joins

8. Outer Join + Outer Join

9. Outer Joins, difference between *= and OUTER JOIN

10. Left-Outer join and Right-Outer join