NULL in Joins

I have converted some legacy SQL to use the newer JOIN syntax, however 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)
Could anyone help me to return a resultset using the new SQL that is
identical to the one produced by the old SQL?
Thanks,
Vince.The conditions in the WHERE clause eliminate all unmatched rows in the join,
and in effect cause the joins to behave as inner joins.
You need to "allow" the unmatched values from the outer table in the where
clause. Try this (untested since you haven't posted DDL and sample data):
WHERE (RLX.RoleID = 1 or RLX.RoleID is null) AND (FT.CultureID = 1)
or
WHERE (RLX.RoleID = 1 or RLX.RoleID is null) AND (FT.CultureID = 1 or
FT.CultureID is null)
ML
http://milambda.blogspot.com/|||Hi Vince
I don't know how the old =, =*, *= syntax is interpreted, as I've never
used it; so I can't guess how it processes the joins. The key thing is
the order in which the joins are processed - which is not necessarily
the order they appear in the SQL statement.
If you have more than one OUTER join, the resultset can vary depending
on which join is performed first. I don't know how the order of
processing is determined - in fact I'm not even sure if it isn't
arbitrary. (Anyone out there happen to know in which order SQL
processes multiple outer joins?)
I avoid this problem by never using more than one OUTER join at any
level of a SELECT statement. This is possible by using subqueries:
instead of
[SQL statement 1]
Table A
INNER JOIN
Table B
ON A.[col]=B.[col]
LEFT JOIN
Table C
ON B.[col]=C.[col]
RIGHT JOIN
Table D
ON C.[col]=D.[col]
which is highly ambiguous, I'd use e.g. (ON statements omitted for
clarity):
[SQL statement 2]
SELECT [columns]
(SELECT [columns] FROM
(SELECT [columns] FROM
Table A
INNER JOIN
Table B) setA
LEFT JOIN
Table C) setB
RIGHT JOIN
Table D
which is one of two different SQL statements that the first statement
_could_ mean.
(The other one is
(Table A INNER JOIN Table B)
LEFT JOIN
(TableC RIGHT JOIN Table D)
)
The problem is that you've got two RIGHT JOINS: one to Links and one to
(Templates INNER JOIN FTK INNER JOIN FT). In a RIGHT join the set of
rows to be returned, in the first instance, is all rows from the 2nd
table - there may or may not be matching rows in the 1st table. With 2
RIGHT JOINS, it's not clear which table should be used to determine the
set of rows - should it be Links or (Templates INNER JOIN FTK INNER
JOIN FT)?
Another way of putting the question is: using the old statement, if you
have 30 rows in Links and 40 rows in (Templates INNER JOIN etc...), do
you end up with 30 rows or 40 rows in the result-set?
For the former (same number of rows as in Links), you'd have to rewrite
the statement as
SELECT [columns] FROM
(SELECT [columns] FROM
RLX
RIGHT JOIN
(Templates INNER JOIN... etc)
) set1
RIGHT JOIN
Links
For the latter, you'd swap the positions of Links and (Templates INNER
JOIN ...):
SELECT [columns] FROM
(SELECT [columns] FROM
RLX
RIGHT JOIN
Links
) set1
RIGHT JOIN
(Templates INNER JOIN... etc) set2
Hope this helps.
cheers
Seb|||VinceKav wrote on Fri, 6 Jan 2006 04:05:01 -0800:

> I have converted some legacy SQL to use the newer JOIN syntax, however 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)
> Could anyone help me to return a resultset using the new SQL that is
> identical to the one produced by the old SQL?
You need to move your WHERE criteria into the joins, or as ML did check for
nulls in the WHERE. Try this:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID FROM RolesToLinksXRef RLX
RIGHT OUTER JOIN Links L ON RLX.LinkID = L.LinkID AND RLX.RoleID = 1
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 AND FT.CultureID = 1
This moves your WHERE criteria to filter the rows before the joins are
evaluated.
Dan|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> I have converted some legacy SQL to use the newer JOIN syntax, however 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)
> Could anyone help me to return a resultset using the new SQL that is
> identical to the one produced by the old SQL?
It only goes to show that the old syntax was confusing. :-)
A tip for the new syntax, is to stick with LEFT JOIN. At least I find
that easier to read. Then you can read the query as you start with
the table in the FROM clause, and then you add the other tables.
Here is my suggestion for a rewrite:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID
FROM Templates T
JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
JOIN ForeignText FT ON FTK.TextID = FT.TextID
LEFT JOIN (RolesToLinksXRef RLX
JOIN Links L RLX.LinkID = L.LinkID)
ON RLX.TempID = T.TempID
AND RLX.RoleID = 1
WHERE FT.CultureID = 1
Note that for the join from RolesToLinksXRef to Links there are
two possibilities. I've assumed that you want an inner join between
these two tables, something that was not possible to express with
the old syntax. The newer syntax permits this, as you can use
parenthesis to control computation order. (The logical order that is.
The optimizer may recast as long as the result is not affected.)
Of course, since I don't have the tables or test data, this may not be
correct.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you all for your help, but I'm still not getting the required results.
I would very much like to understand the relationship between the old and ne
w
SQL syntax so that I can apply it to other legacy code. I have supplied mor
e
information relating to the table relationships and the output.
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 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:
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 rows so I can see which links have or have not been
allocated.
Many thanks in advance,
Vince Kavanagh.
"Erland Sommarskog" wrote:

> VinceKav (VinceKav@.discussions.microsoft.com) writes:
> It only goes to show that the old syntax was confusing. :-)
> A tip for the new syntax, is to stick with LEFT JOIN. At least I find
> that easier to read. Then you can read the query as you start with
> the table in the FROM clause, and then you add the other tables.
> Here is my suggestion for a rewrite:
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM Templates T
> JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> JOIN ForeignText FT ON FTK.TextID = FT.TextID
> LEFT JOIN (RolesToLinksXRef RLX
> JOIN Links L RLX.LinkID = L.LinkID)
> ON RLX.TempID = T.TempID
> AND RLX.RoleID = 1
> WHERE FT.CultureID = 1
>
> Note that for the join from RolesToLinksXRef to Links there are
> two possibilities. I've assumed that you want an inner join between
> these two tables, something that was not possible to express with
> the old syntax. The newer syntax permits this, as you can use
> parenthesis to control computation order. (The logical order that is.
> The optimizer may recast as long as the result is not affected.)
> Of course, since I don't have the tables or test data, this may not be
> correct.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> Thank you all for your help, but I'm still not getting the required
> results.
The standard recommendation is that you should include:
o CREATE TABLE statments for your tables.
o INSERT statements with sample data.
o The desired result without the sample.
Without that, you will be more or less good guesses.

> 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'm afraid that very few can help you with that. It's not that we don't
understand the new syntax. But we have forgotten how the old syntax
worked - if we ever understood it.

> 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.
So a wild guess based from the sample output, is that this query might
work:
SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
RLX.RolesToLinksXRefID
FROM Templates T
JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
JOIN ForeignText FT ON FTK.TextID = FT.TextID
CROSS JOIN Links L
LEFT JOIN RolesToLinksXRef RLX ON RLX.LinkID = L.LinkID)
AND RLX.TempID = T.TempID
AND RLX.RoleID = 1
WHERE FT.CultureID = 1
Thanks to the use of CROSS JOIN, this has to be stamped as an
"unusual query".
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
Hi Erland,
Thanks very much for your help, the last query you sent me worked, I had
also tried the CROSS join approach but that resulted in too many records.
However as you advised I have posted the table creation and data insert
scripts.
I have been working with SQL for many years, but this is the first time I
have been so exasperated. Have you written any articles on the methodology
to use when designing queries that use multiple joins?
--Table creation scripts
--**********************
CREATE TABLE dbo.Templates
(
TempID int NULL,
Title nvarchar(50) NULL
)
GO
CREATE TABLE dbo.ForeignTextKey
(
TextID int NULL,
TextCode nvarchar(50) NULL
)
GO
CREATE TABLE dbo.ForeignText
(
TextID int NULL,
CultureID int NULL,
Message nvarchar(100) NULL
)
GO
CREATE TABLE dbo.Links
(
LinkID int NULL,
LinkCaption nvarchar(50) NULL
)
GO
CREATE TABLE dbo.RolesToLinksXRef
(
RolesToLinksXRefID int NULL,
LinkID int NULL,
TempID int NULL,
RoleID int NULL
)
GO
--Data inserts
--************
--Links data
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(1, 'ADD')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(2, 'EDIT')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(3, 'VIEW')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(4, 'COPY')
INSERT INTO [Links]([LinkID], [LinkCaption])VALUES(5, 'DELETE')
--ForeignTextKey data
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(1, 'FTK_001')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(2, 'FTK_002')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(3, 'FTK_003')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(4, 'FTK_004')
INSERT INTO [ForeignTextKey]([TextID], [TextCode])VALUES(5, 'FTK_005')
--ForeignText data
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(1, 1,
'Template1')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(2, 1,
'Template2')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(3, 1,
'Template3')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(4, 1,
'Template4')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(5, 1,
'Template5')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(1, 2,
'Calibre1')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(2, 2,
'Calibre2')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(3, 2,
'Calibre3')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(4, 2,
'Calibre4')
INSERT INTO [ForeignText]([TextID], [CultureID], [Message])VALUES(5, 2,
'Calibre5')
--Template data
INSERT INTO [Templates]([TempID], [Title])VALUES(1,'FTK_001')
INSERT INTO [Templates]([TempID], [Title])VALUES(2,'FTK_002')
INSERT INTO [Templates]([TempID], [Title])VALUES(3,'FTK_003')
INSERT INTO [Templates]([TempID], [Title])VALUES(4,'FTK_004')
INSERT INTO [Templates]([TempID], [Title])VALUES(5,'FTK_005')
--RolesToLinksXRef data
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(1, 1, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(2, 2, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(3, 3, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(4, 4, 1, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(5, 1, 2, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(6, 2, 2, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(7, 1, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(8, 2, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(9, 3, 3, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(10, 1, 4, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(11, 2, 4, 1)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(12, 1, 1, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(13, 2, 1, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(14, 1, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(15, 2, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(16, 3, 2, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(17, 1, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(18, 2, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(19, 3, 3, 2)
INSERT INTO [RolesToLinksXRef]([RolesToLinksXRefID],
[LinkID], [TempID],
[RoleID])VALUES(20, 4, 3, 2)
/*
This is the desired result in that it displays which links have been applied
to templates for a specific role.
Desired Result
**************
TempID Message LinkID LinkCaption Roles
ToLinkXRefID
-- -- -- -- --
1 Template1 1 ADD 1
1 Template1 2 EDIT 2
1 Template1 3 VIEW 3
1 Template1 4 COPY 4
1 Template1 5 DELETE NULL
2 Template2 1 ADD 5
2 Template2 2 EDIT 6
2 Template2 3 VIEW NULL
2 Template2 4 COPY NULL
2 Template2 5 DELETE NULL
3 Template3 1 ADD 7
3 Template3 2 EDIT 8
3 Template3 3 VIEW 9
3 Template3 4 COPY NULL
3 Template3 5 DELETE NULL
4 Template4 1 ADD 10
4 Template4 2 EDIT 11
4 Template4 3 VIEW NULL
4 Template4 4 COPY NULL
4 Template4 5 DELETE NULL
5 Template5 1 ADD NULL
5 Template5 2 EDIT NULL
5 Template5 3 VIEW NULL
5 Template5 4 COPY NULL
5 Template5 5 DELETE NULL
*/
Once again many thanks,
Vince.
"Erland Sommarskog" wrote:

> VinceKav (VinceKav@.discussions.microsoft.com) writes:
> The standard recommendation is that you should include:
> o CREATE TABLE statments for your tables.
> o INSERT statements with sample data.
> o The desired result without the sample.
> Without that, you will be more or less good guesses.
>
> I'm afraid that very few can help you with that. It's not that we don't
> understand the new syntax. But we have forgotten how the old syntax
> worked - if we ever understood it.
>
> So a wild guess based from the sample output, is that this query might
> work:
> SELECT T.TempID, FT.Message, L.LinkID, L.LinkCaption,
> RLX.RolesToLinksXRefID
> FROM Templates T
> JOIN ForeignTextKey FTK ON T.Title = FTK.TextCode
> JOIN ForeignText FT ON FTK.TextID = FT.TextID
> CROSS JOIN Links L
> LEFT JOIN RolesToLinksXRef RLX ON RLX.LinkID = L.LinkID)
> AND RLX.TempID = T.TempID
> AND RLX.RoleID = 1
> WHERE FT.CultureID = 1
> Thanks to the use of CROSS JOIN, this has to be stamped as an
> "unusual query".
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||On Wed, 11 Jan 2006 04:47:04 -0800, VinceKav wrote:

>
>Hi Erland,
>Thanks very much for your help, the last query you sent me worked, I had
>also tried the CROSS join approach but that resulted in too many records.
>However as you advised I have posted the table creation and data insert
>scripts.
Hi Vince,
Thanks for posting the repro code. The query below returns the results
you need:
SELECT t.TempID, ft.Message,
l.LinkID, l.LinkCaption,
x.RolesToLinksXRefID
FROM ForeignText AS ft
INNER JOIN ForeignTextKey AS ftk
ON ftk.TextID = ft.TextID
INNER JOIN Templates AS t
ON t.Title = ftk.TextCode
CROSS JOIN Links AS l
LEFT JOIN RolesToLinksXRef AS x
ON x.TempID = t.TempID
AND x.LinkID = l.LinkID
AND x.RoleID = 1
WHERE ft.CultureID = 1
ORDER BY t.TempID, l.LinkID
(BTW, I just went back to Erlands previous message, and after removing
the extraneous closing parenthesis, his query gives the exact same
results).
Hugo Kornelis, SQL Server MVP|||VinceKav (VinceKav@.discussions.microsoft.com) writes:
> Thanks very much for your help, the last query you sent me worked, I had
> also tried the CROSS join approach but that resulted in too many records.
> However as you advised I have posted the table creation and data insert
> scripts.
> I have been working with SQL for many years, but this is the first time
> I have been so exasperated. Have you written any articles on the
> methodology to use when designing queries that use multiple joins?
Egads, no! That is not an article I would want to write about. The
foremost important when designing queries is to know your data model.
As I mentioned, you query was a bit odd, but chance had it that I had
a similar case at work recently. One of junior developers was tasked
to go through all stored procedures with the old-style join as a preparation
for SQL 2005. When she ran into difficulties, she sent me the query
and asked for help. One of the queries was translated to something
similar to yours. What I recall was that the procedure included some
commented piece of code with ANSI join, and apparently the original
author had given up with the ANSI join and used the legacy syntax. And
it took me some time to get the query right as well, and then I was
fortunate to know the tables.
It is the CROSS JOIN that makes it looks so unusual. The old syntax
is much better of hiding cross joins so that you don't see them.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx