Complex Query to Create View

Hello Everyone...
How in the world does this query work? It is used to create a view, but
I've never seen so many mismatched JOIN pairs, and trying to decipher them i
s
difficult. I've looked at the design view, and also the query execution pla
n
and the showplan_text/all also, but I am no colser to figuring it out.
Several people have told me to re-write it, but it is difficult to know wher
e
to start. Below is a copy of the query...Help!!!
Thanks.
Tomislav
SELECT dbo.Constituents.DisplayName, dbo.ObjComponents.ComponentNumber,
dbo.ObjComponents.ComponentName, dbo.Objects.Dated,
dbo.ObjComponents.ReceivedDate, dbo.Objects.Medium,
dbo.Objects.Dimensions, dbo.ObjTitles.Title, dbo.Objects.ObjectID,
dbo.Objects.ObjectName,
dbo.Objects.ObjectNumber,
dbo.Constituents.ConstituentID, dbo.ObjComponents.SortNumber AS
CompSortNumber, dbo.Objects.SortNumber,
CurLoc.Site AS CurSite, CurLoc.Room AS CurRoom,
CurLoc.UnitType AS CurUnitType, CurLoc.UnitNumber AS CurUnitNumber,
CurLoc.UnitPosition AS CurUnitPosition,
PrevLoc.UnitType AS PrevUnitType, PrevLoc.UnitNumber AS PrevUnitNumber,
PrevLoc.Site AS PrevSite,
PrevLoc.Room AS PrevRoom, PrevLoc.UnitPosition AS
PrevUnitPosition, dbo.Objects.DepartmentID, CurObjLoc.Handler,
CurObjLoc.TransStatusID,
CurObjLoc.TransCodeID, CurObjLoc.TempText AS
CurrentLocRemarks, PrevObjLoc.TempText AS PreviouslocRemarks
FROM dbo.Locations CurLoc RIGHT OUTER JOIN
dbo.Locations PrevLoc RIGHT OUTER JOIN
dbo.ObjLocations PrevObjLoc ON PrevLoc.LocationID =
PrevObjLoc.LocationID RIGHT OUTER JOIN
dbo.Objects INNER JOIN
dbo.ObjComponents ON dbo.ObjComponents.ObjectID =
dbo.Objects.ObjectID INNER JOIN
dbo.ObjLocations CurObjLoc ON
dbo.ObjComponents.CurrentObjLocID = CurObjLoc.ObjLocationID ON
PrevObjLoc.ObjLocationID = CurObjLoc.PrevObjLocID ON
CurLoc.LocationID = CurObjLoc.LocationID LEFT OUTER JOIN
dbo.ObjTitles ON dbo.Objects.ObjectID =
dbo.ObjTitles.ObjectID AND dbo.ObjTitles.DisplayOrder = 1 LEFT OUTER JOIN
dbo.Roles INNER JOIN
dbo.ConXrefs ON dbo.Roles.RoleID = dbo.ConXrefs.RoleID
AND dbo.Roles.RoleTypeID = 1 INNER JOIN
dbo.Constituents ON dbo.ConXrefs.ConstituentID =
dbo.Constituents.ConstituentID ON dbo.Objects.ObjectID = dbo.ConXrefs.ID AND
dbo.ConXrefs.DisplayOrder = 1
WHERE (CurObjLoc.TransCodeID < 4)Take code like this to a SQL formatter (I used this one:
http://www.wangz.net/cgi-bin/pp/gsq...p/sqlformat.tpl) and you will
see the code isn't so messy. It is not so bad (it is pretty bad!) What do
you mean by mismatched JOIN pairs. You can nest joins to control their
order (mathmatically) to deal with inner and other join needs.
Once formatted, you can see it a bit clearer and perhaps make some sense of
it, based on the data model. Without that it is a really big mess.
Good luck :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Tomislav" <Tomislav@.discussions.microsoft.com> wrote in message
news:AD426402-D7A4-4F5E-8BBD-C89266492748@.microsoft.com...
> Hello Everyone...
> How in the world does this query work? It is used to create a view, but
> I've never seen so many mismatched JOIN pairs, and trying to decipher them
> is
> difficult. I've looked at the design view, and also the query execution
> plan
> and the showplan_text/all also, but I am no colser to figuring it out.
> Several people have told me to re-write it, but it is difficult to know
> where
> to start. Below is a copy of the query...Help!!!
> Thanks.
> Tomislav
>
> SELECT dbo.Constituents.DisplayName,
> dbo.ObjComponents.ComponentNumber,
> dbo.ObjComponents.ComponentName, dbo.Objects.Dated,
> dbo.ObjComponents.ReceivedDate, dbo.Objects.Medium,
> dbo.Objects.Dimensions, dbo.ObjTitles.Title, dbo.Objects.ObjectID,
> dbo.Objects.ObjectName,
> dbo.Objects.ObjectNumber,
> dbo.Constituents.ConstituentID, dbo.ObjComponents.SortNumber AS
> CompSortNumber, dbo.Objects.SortNumber,
> CurLoc.Site AS CurSite, CurLoc.Room AS CurRoom,
> CurLoc.UnitType AS CurUnitType, CurLoc.UnitNumber AS CurUnitNumber,
> CurLoc.UnitPosition AS CurUnitPosition,
> PrevLoc.UnitType AS PrevUnitType, PrevLoc.UnitNumber AS PrevUnitNumber,
> PrevLoc.Site AS PrevSite,
> PrevLoc.Room AS PrevRoom, PrevLoc.UnitPosition AS
> PrevUnitPosition, dbo.Objects.DepartmentID, CurObjLoc.Handler,
> CurObjLoc.TransStatusID,
> CurObjLoc.TransCodeID, CurObjLoc.TempText AS
> CurrentLocRemarks, PrevObjLoc.TempText AS PreviouslocRemarks
> FROM dbo.Locations CurLoc RIGHT OUTER JOIN
> dbo.Locations PrevLoc RIGHT OUTER JOIN
> dbo.ObjLocations PrevObjLoc ON PrevLoc.LocationID =
> PrevObjLoc.LocationID RIGHT OUTER JOIN
> dbo.Objects INNER JOIN
> dbo.ObjComponents ON dbo.ObjComponents.ObjectID =
> dbo.Objects.ObjectID INNER JOIN
> dbo.ObjLocations CurObjLoc ON
> dbo.ObjComponents.CurrentObjLocID = CurObjLoc.ObjLocationID ON
> PrevObjLoc.ObjLocationID = CurObjLoc.PrevObjLocID ON
> CurLoc.LocationID = CurObjLoc.LocationID LEFT OUTER JOIN
> dbo.ObjTitles ON dbo.Objects.ObjectID =
> dbo.ObjTitles.ObjectID AND dbo.ObjTitles.DisplayOrder = 1 LEFT OUTER JOIN
> dbo.Roles INNER JOIN
> dbo.ConXrefs ON dbo.Roles.RoleID =
> dbo.ConXrefs.RoleID
> AND dbo.Roles.RoleTypeID = 1 INNER JOIN
> dbo.Constituents ON dbo.ConXrefs.ConstituentID =
> dbo.Constituents.ConstituentID ON dbo.Objects.ObjectID = dbo.ConXrefs.ID
> AND
> dbo.ConXrefs.DisplayOrder = 1
> WHERE (CurObjLoc.TransCodeID < 4)
>
>|||Louis...
Thank you for the SQL Formatter...Amazing how it looks after that handy
little format.
What I don't understand is how the query works in regards to having
something like...
table1 right outer join
table2 right outer join...etc...
This is what is confusing to me.
Tomislav
"Louis Davidson" wrote:

> Take code like this to a SQL formatter (I used this one:
> http://www.wangz.net/cgi-bin/pp/gsq...p/sqlformat.tpl) and you wi
ll
> see the code isn't so messy. It is not so bad (it is pretty bad!) What
do
> you mean by mismatched JOIN pairs. You can nest joins to control their
> order (mathmatically) to deal with inner and other join needs.
> Once formatted, you can see it a bit clearer and perhaps make some sense o
f
> it, based on the data model. Without that it is a really big mess.
> Good luck :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
>|||Not my formatter (wangz.net?) but it is handy.
You can do this to make a join only affect a part of the query set:
select columns
from table1
right outer join table2
join table3
on table2.key = table3.key
on table1.key = table2.key
Now, the join between table2 and table3 logically happens first, so, in
essence you end up with
select columns
from table1
right outer join <table2JoinTable3>
on table1.key = <table2JoinTable3>.table2.key
So even if no values were returned there, you would still get the values in
table1. The indention is not totally necessary in a simply query like this,
but, as the number of tables grows this can be useful. Hope this helps to
get you started. If you want more help, post the table structures and
someone will build the db and take a look at the structures in context. It
might help to understand (though I am not sure about that :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Tomislav" <Tomislav@.discussions.microsoft.com> wrote in message
news:9CFD64C4-83D4-4E04-9B8D-34535ADA868A@.microsoft.com...
> Louis...
> Thank you for the SQL Formatter...Amazing how it looks after that handy
> little format.
> What I don't understand is how the query works in regards to having
> something like...
> table1 right outer join
> table2 right outer join...etc...
> This is what is confusing to me.
> Tomislav
> "Louis Davidson" wrote:
>
>|||Louis...
In a stange way, this kind of makes sense to me. The one thing I don't
understand yet, is the line
on table1.key = <table2JoinTable3>.table2.key
Shouldn't it be on table1.key = <table2JoinTable3>.key ?
If not, can you explain to me why not? I've been thrown from the edge of
the campground in to the fire with my sql database.
BTW, I really do appreciate your help.
Thanks,
Tomislav
"Louis Davidson" wrote:

> Not my formatter (wangz.net?) but it is handy.
> You can do this to make a join only affect a part of the query set:
> select columns
> from table1
> right outer join table2
> join table3
> on table2.key = table3.key
> on table1.key = table2.key
> Now, the join between table2 and table3 logically happens first, so, in
> essence you end up with
> select columns
> from table1
> right outer join <table2JoinTable3>
> on table1.key = <table2JoinTable3>.table2.key
> So even if no values were returned there, you would still get the values i
n
> table1. The indention is not totally necessary in a simply query like thi
s,
> but, as the number of tables grows this can be useful. Hope this helps to
> get you started. If you want more help, post the table structures and
> someone will build the db and take a look at the structures in context. I
t
> might help to understand (though I am not sure about that :)
> --|||It is sort of, but there is not actually a <table2JoinTable3> and it is not
part of the syntax or anything, just there to remind you that the set is
created by the join. The names of the columns in the sets still are
referenced by the tablename. So the set: actually looks like:
table1.column1 table1.column2 table2.column1 table2.column2
And so on. I probably should have made that more clear, and I hope that
makes it a bit more :)

> If not, can you explain to me why not? I've been thrown from the edge of
> the campground in to the fire with my sql database.
That query is quite a fire too. Someone was hitting the hard stuff when
they wrote that query.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Tomislav" <Tomislav@.discussions.microsoft.com> wrote in message
news:C75649BC-9C9B-449F-A8F1-2FEA764124D0@.microsoft.com...
> Louis...
> In a stange way, this kind of makes sense to me. The one thing I don't
> understand yet, is the line
> on table1.key = <table2JoinTable3>.table2.key
> Shouldn't it be on table1.key = <table2JoinTable3>.key ?
> If not, can you explain to me why not? I've been thrown from the edge of
> the campground in to the fire with my sql database.
> BTW, I really do appreciate your help.
> Thanks,
> Tomislav
>
> "Louis Davidson" wrote:
>
>|||Well, it doesn't really clear it up for me, sorry to say. What i would like
to do is to try and rewrite this query into something more logical, but
looking at the graphical design of the view, it is confusing.
I've talked with someone at the company that wrote this query and all they
tell me is that the main table in it is Objects. That really helps, but it
is easier to offload re-writing to a client if they really want to, I guess.
Thanks for all of your help, Louis. I think that I am at least going in the
right direction in my understanding of this query.
Tomislav
"Louis Davidson" wrote:

> It is sort of, but there is not actually a <table2JoinTable3> and it is no
t
> part of the syntax or anything, just there to remind you that the set is
> created by the join. The names of the columns in the sets still are
> referenced by the tablename. So the set: actually looks like:
> table1.column1 table1.column2 table2.column1 table2.column2
> And so on. I probably should have made that more clear, and I hope that
> makes it a bit more :)
>
> That query is quite a fire too. Someone was hitting the hard stuff when
> they wrote that query.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "Tomislav" <Tomislav@.discussions.microsoft.com> wrote in message
> news:C75649BC-9C9B-449F-A8F1-2FEA764124D0@.microsoft.com...
>
>|||My advice to make this more readable is to break it down into 2 or 3 views,
then join the views together in the code.
This won't necesarily simplify the code, but it will break it down into
smaller pieces which will be easier to digest.
Looking at 3 joins at a time is much easier than looking at 10 joins.|||I was thinking of doing this...How would I join each view in the code? Is
this where a subquery would come into play? I'm more than willing to give i
t
a try, but I'm going to have read up on a few topics before I can do it.
"Jim Underwood" wrote:

> My advice to make this more readable is to break it down into 2 or 3 views
,
> then join the views together in the code.
> This won't necesarily simplify the code, but it will break it down into
> smaller pieces which will be easier to digest.
> Looking at 3 joins at a time is much easier than looking at 10 joins.
>
>|||Just the same way you would any other table. Just take the bits that join
together in a sub-join (not sure that is the proper term, but...) So from a
query like this you could just:
select columns
from table1
right outer join table2
join table3
on table2.key = table3.key
on table1.key = table2.key
create view as
select columnListNeeded
from table2
join table3
on table2.key = table3.key
select columns
from table1
right outer join view
on table1.key = table2.key
If you are using 2005, consider just using CTE's for this purpose, rather
than making full views that will only be used for this purpose. Once you
figure out the data, you may end up putting it back into one query (this
time adding comments to explain why you wrote such a crazy query that no one
else could figure out :)
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Tomislav" <Tomislav@.discussions.microsoft.com> wrote in message
news:4174389E-681C-4F27-A14E-FBCC7FF1013F@.microsoft.com...
>I was thinking of doing this...How would I join each view in the code? Is
> this where a subquery would come into play? I'm more than willing to give
> it
> a try, but I'm going to have read up on a few topics before I can do it.
> "Jim Underwood" wrote:
>