Nesting views?

OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David

Nesting views?

OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David

nested joins - joining one table to another multiple times

Hi,

I'm having problems constructing a nested join. It's quite complex, so
here's a simplfied example of the problem. Any thoughts on what I'm
doig wrong - or if I've got the whole approach wrong are welcome.

I've two tables :-

one is a contact table contacting name, addresses etc. Three of the
fields represent users - 'created by', 'last modified by' and 'owner'.
They contain usernames - eg. JDOE, BSMITH etc.

The other table contants usernames and new ID codes.

What I want to do is create a new dataset by joining the contacts table
with the user table on all three fields - so the new dataset contains
the ids for the creator, last modifier and owner.

I've tried things similar to:

select c.*, u1.id, u2,id, u3.id
from contact c
left outer join users u1
left outer join users u2
left outer join users u3
on (u3.username = c.owner)
on (u2.username = c.modified)
on (u1.username = c.creator )

But it compains that
"The column prefix 'c' does not match with a table name or alias name
used in the query."

The problem is referencing c (contact) through the whole set of joins.

I would like to do this in some similar format as the query is within a
cursor and post-processing would be very long-winded.

ThanksHi Steve,

this sql-code will work:

select c.*, u1.id, u2.id, u3.id
from contact c
left outer join users u1 ON u1.username = c.creator
left outer join users u2 ON u2.username = c.modified
left outer join users u3 ON u3.username = c.owner

Steve C schrieb:
...
> select c.*, u1.id, u2,id, u3.id
> from contact c
> left outer join users u1
> left outer join users u2
> left outer join users u3
> on (u3.username = c.owner)
> on (u2.username = c.modified)
> on (u1.username = c.creator )
> But it compains that
> "The column prefix 'c' does not match with a table name or alias name
> used in the query."

www.zankl-it.de

Nested Inner Joins

Hi folks,

I have three tables:

ActivityCalls, UserList, and People

UserList.Username and ActivityCalls.Username are 'INNER JOINED' to determine which user is attached to an activity.

What I want to do is go one step further and look up the People.PersonName from people.

Here's what I have so far but it doesn't seem to work... Am I on the wrong track here?

INNER JOIN (People INNER JOIN UserList ON UserList.User_Username=ActivityCalls.Username)
ON People.Person_ID=UserList.Person_ID

Any help would be greatly appreciated.

Thanks

Nick

Quote:

Originally Posted by nkew

Hi folks,

I have three tables:

ActivityCalls, UserList, and People

UserList.Username and ActivityCalls.Username are 'INNER JOINED' to determine which user is attached to an activity.

What I want to do is go one step further and look up the People.PersonName from people.

Here's what I have so far but it doesn't seem to work... Am I on the wrong track here?

INNER JOIN (People INNER JOIN UserList ON UserList.User_Username=ActivityCalls.Username)
ON People.Person_ID=UserList.Person_ID

Any help would be greatly appreciated.

Thanks

Nick

Hi Nick
This might work:

FROM UserList U
INNER JOIN ActivityCalls A
ON U.User_Username = A.Username
INNER JOIN People P
ON U.User_PersonID = P.Person_ID