writing a multi-table view

Hello,

I am trying to put write a view for multiple tables but have minimal experience in writing views. Can you provide a template that I can work off?

Here is what I am trying to accomplish....

There are 8 tables involved:
Table_1
Table_2
Table_3
Table_4
Table_5
Table_6
Table_7
Table_8

 I need to write a view to display the following information:

type_id (table 6 and 3)
type (in tables 6 and 7)
description - of entities associated with type (table 7)
their id ranges start (table 2)
their id ranges end (table 2)
hostnames (if any)(table 2)
host_id( table 3)


I will need to use the deliverable date in Table_3 to show only those that are current.


Thanks for help.
cassie5643Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
that's fairly vague but constructing a view is fairly simple.

Write your query and test it, then put "create view your_view as"  in front of it and you're done.

maybe something like ....

create view your_view
as
select table_6.type_id, table_6.type, table_7.description, table_2.rangestart,table_2.rangeend,table_2.hostnamnes,table_3.host_id
from Table_1,
Table_2,
Table_3,
Table_4,
Table_5,
Table_6,
Table_7,
Table_8
where table_1.some_column = table_2.some_column
and table_2.some_other_column = table_3.some_other_column  --- put your join conditions here
and table_3.deliverable_date >= trunc(sysdate)   -- put your date criteria that defines what "current" is here


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
Start small.  First just build and test a two table join that involves two of these tables.  Then add one table at a time and test after each change to make sure the results are still correct *AND* that the view is efficient!  You may need to use Oracle's "trace" utility for this.  This could be "autotrace" in SQL*Plus or, if you use a GUI tool like TOAD or SQL Developer, you should be able to find an "explain plan" or similar feature there to check the efficiency of your view at each step.

Be aware that if your data is incomplete (or not validated) you may need to use outer join(s) instead of simple joins for some or all of these joins to make sure that your view does not fail to show you records that have no matching data in the other table(s).
0
cassie5643Author Commented:
Thanks! Very helpful :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.