[Tutor] DB design

[Bill Kranec]

Liam,

I think what you want is called a view.  A view is a memory based table 
defined by a query as follows:

CREATE VIEW myview (
    column1,
    column2,
    ...   )
AS
BEGIN
SELECT * FROM table1
END;

In this example, you can now SELECT * FROM myview, and get table1.  You 
can put joined tables or use SELECT UNION inside the BEGIN, END 
statements to make the view larger, or use WHERE clauses to make the 
view a subset of the original table.

If the data in the underlying tables changes, the view will change also.

So by querying a view, you are querying the results of a query, in a 
sense, but remember that the database itself will always query the 
underlying tables, since the view doesn't physically exist.

I hope I've been helpful.  Just ask if I didn't explain anything clearly.

Bill


Liam Clarke wrote:

>Hi, 
>
>Working my way through the basics of SQL, and I can see that it's very
>powerful for searching by different criteria.
>
>I'm already hitting my conceptual troubles however, as I'm visualising
>each table as a 'card'.
>Always my problems, too much imagination. But yeah, it seems very 1
>dimensional. But what I was wondering was is it possible within the
>bounds of SQL to contain a SQL query reference as a value, so that
>retrieving a certain value retrieves the results of that query i.e.
>
>table foo
>
>a b c
>
>1  2 select d from bar
>
>table bar
>
>d e f
>
>3 4 5 
>
>and select c from foo retrieves d from bar? I know I'm leapfrogging
>way ahead of myself, and probably confusing myself unnecessarily, but
>I'm just trying to make my cards more 3D in terms of linking data....
>
>Any assistance for my vague question gratefully appreciated.
>
>Regards, 
>
>Liam Clarke
>
>  
>