NULL IN JOINS

Thanks to everyone who applied to my orginal question (1/6/2006), however I'
m
still not getting the required results. I would very much like to understand
the relationship between the old and new SQL syntax so that I can apply it t
o
other legacy code. I have supplied more information relating to the table
relationships and the output.
The original SQL returns the required NULL values for the RolesToLinksXRefID
column, but the newer SQL doesn't.
--OLD SQL
Select T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID
From Templates T, ForeignTextKey FTK, ForeignText FT, Links L,
RolesToLinksXRef RLX
Where T.Title = FTK.TextCode
And FTK.TextID = FT.TextID
And RLX.LinkID =* L.LinkID
And RLX.TempID =* T.TempID
And RLX.RoleID = 1
And FT.CultureID = 1
--NEW SQL
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID
FROM RolesToLinksXRef RLX
RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
RIGHT OUTER JOIN Templates T
INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID =
T.TempID
WHERE (RLX.RoleID = 1) AND (FT.CultureID = 1)
The tables used in the query are related as follows:
The RolesToLinksXRef table holds a foreign key to the following tables:
1. Links on RolesToLinksXRef.LinkID = Links.LinkID
2. Templates on RolesToLinksXRef.TempID = Templates.TempID
The Templates table holds a foreign key to the following tables:
1. ForeignTextKey on Templates.Title = ForeignTextKey.TextCode
The ForeignTextKey table holds a foreign key to the following tables:
1. ForeignText on ForeignTextKey.TextID = ForeignText.TextID
All the answers I have received so far produce much the same results,
however none produce the same results as the old SQL.
The old SQL produces the following correct result set:
TempID Message LinkID LinkCaption RolesToLinksXRefID
60 Sign In 1 ADD NULL
60 Sign Out 2 EXIT NULL
60 Edit User 3 EDIT NULL
and the new SQL produces this result set (only one row):
TempID Message LinkID LinkCaption RolesToLinksXRefID
60 Sign In NULL NULL NULL
The purpose of the query is to return all the Templates.TempID, the
ForeignText.Message, the Links.LinkID, the Links.Caption and the
RolesToLinksXRef.RolesToLinksXRefID for a specific RolesToLinksXRef.RoleID,
as well as all the links on templates that the role has not been allocated t
o.
A template may have 6 links, however the role may only have been allocated
3, I need to return all 6 rows so I can see which links have or have not bee
n
allocated.
Thanks in advance,
Vince Kavanagh.To help us stop guessing how to translate the old-school joins (which are in
effect CROSS joins) to contemporary syntax, please post DDL, sample data and
expected results.
Or, if you want to investigate this yourself, first write explicit CROSS
joins to tables and alter them appropriately to either INNER or OUTER joins
-
one at a time.
ML
http://milambda.blogspot.com/|||Have you tried changing your right outer joins to left outer joins? I'm not
certain but I think this is the problem.
Also, in your second SQL part of the join clause appears to be missing.
Try changing
> RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
> RIGHT OUTER JOIN Templates T
to
> LEFT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
> LEFT OUTER JOIN Templates T ON RLX.TempID = T.TempID
or, if I am mistaken about the left/right outer join, just try
> RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
> RIGHT OUTER JOIN Templates T ON RLX.TempID = T.TempID
"VinceKav" <VinceKav@.discussions.microsoft.com> wrote in message
news:FB19EA67-3517-45F9-A34B-26899E4ADB12@.microsoft.com...
> Thanks to everyone who applied to my orginal question (1/6/2006), however
I'm
> still not getting the required results. I would very much like to
understand
> the relationship between the old and new SQL syntax so that I can apply it
to
> other legacy code. I have supplied more information relating to the table
> relationships and the output.
> The original SQL returns the required NULL values for the
RolesToLinksXRefID
> column, but the newer SQL doesn't.
> --OLD SQL
> Select T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> From Templates T, ForeignTextKey FTK, ForeignText FT, Links L,
> RolesToLinksXRef RLX
> Where T.Title = FTK.TextCode
> And FTK.TextID = FT.TextID
> And RLX.LinkID =* L.LinkID
> And RLX.TempID =* T.TempID
> And RLX.RoleID = 1
> And FT.CultureID = 1
> --NEW SQL
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM RolesToLinksXRef RLX
> RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID
> RIGHT OUTER JOIN Templates T
> INNER JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> INNER JOIN ForeignText FT ON FTK.TextID = FT.TextID ON RLX.TempID =
> T.TempID
> WHERE (RLX.RoleID = 1) AND (FT.CultureID = 1)
>
> The tables used in the query are related as follows:
> The RolesToLinksXRef table holds a foreign key to the following tables:
> 1. Links on RolesToLinksXRef.LinkID = Links.LinkID
> 2. Templates on RolesToLinksXRef.TempID = Templates.TempID
> The Templates table holds a foreign key to the following tables:
> 1. ForeignTextKey on Templates.Title = ForeignTextKey.TextCode
> The ForeignTextKey table holds a foreign key to the following tables:
> 1. ForeignText on ForeignTextKey.TextID = ForeignText.TextID
>
> All the answers I have received so far produce much the same results,
> however none produce the same results as the old SQL.
> The old SQL produces the following correct result set:
> TempID Message LinkID LinkCaption RolesToLinksXRefID
> 60 Sign In 1 ADD NULL
> 60 Sign Out 2 EXIT NULL
> 60 Edit User 3 EDIT NULL
> and the new SQL produces this result set (only one row):
> TempID Message LinkID LinkCaption RolesToLinksXRefID
> 60 Sign In NULL NULL NULL
>
> The purpose of the query is to return all the Templates.TempID, the
> ForeignText.Message, the Links.LinkID, the Links.Caption and the
> RolesToLinksXRef.RolesToLinksXRefID for a specific
RolesToLinksXRef.RoleID,
> as well as all the links on templates that the role has not been allocated
to.
> A template may have 6 links, however the role may only have been allocated
> 3, I need to return all 6 rows so I can see which links have or have not
been
> allocated.
> Thanks in advance,
> Vince Kavanagh.