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:

Show quoteHide quoteYou 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 (Vince***@discussions.microsoft.com) writes:
Show quoteHide quoteIt 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.

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 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 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 to.

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.



Show quoteHide quote

VinceKav (Vince***@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".

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    RolesToLinkXRefID
------    -------        ------    -----------    -----------------
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.

Show quoteHide quote

On Wed, 11 Jan 2006 04:47:04 -0800, VinceKav wrote:

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).

VinceKav (Vince***@discussions.microsoft.com) writes:
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.

You can fix this with the other postings, but you might want to look at
doing some re-writes.  Here is how OUTER JOINs work in SQL-92.  Assume
you are given:

Table1       Table2
a   b        a   c
======       ======
1   w        1   r
2   x        2   s
3   y        3   t
4   z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a      <== join condition
    AND Table2.c = 't';      <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query.  What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables.  Scan each row in the
result set.

2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @ = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a   b        a   c
=========================
1   w       1   r @
1   w       2   s
1   w       3   t *
2   x       1   r
2   x       2   s @
2   x       3   t *
3   y       1   r
3   y       2   s
3   y       3   t @* <== the TRUE set
4   z       1   r
4   z       2   s
4   z       3   t *

Table1 LEFT OUTER JOIN Table2
a   b        a   c
=========================
3   y     3      t      <= only TRUE row
-----------------------
1   w     NULL   NULL   Sets of duplicates
1   w     NULL   NULL
1   w     NULL   NULL
-----------------------
2   x     NULL   NULL
2   x     NULL   NULL
2   x     NULL   NULL
3   y     NULL   NULL  <== derived from the TRUE set - Remove
3   y     NULL   NULL
-----------------------
4   z     NULL   NULL
4   z     NULL   NULL
4   z     NULL   NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a   b        a   c
=========================
1   w     NULL   NULL
2   x     NULL   NULL
3   y     3      t
4   z     NULL   NULL

The basic rule is that every row in the preserved table is represented
in the results in at least one result row.

There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products.  Consider the two Chris Date tables

Suppliers        SupParts
supno             supno partno qty
=========        ==============
S1               S1   P1    100
S2               S1   P2    250
S3               S2   P1    100
                  S2   P2    250

and let's do an extended equality outer join like this:

SELECT *
  FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
   AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1     S1   P1    100
S1     S1   P2    250
S2     S2   P1    100
S2     S2   P2    250
S3   NULL  NULL   NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1   S1   P1    100
S2   S2   P1    100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1   S1   P1    100
S2   S2   P1    100
S3   NULL NULL  NULL

Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds!  In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...

SELECT *
   FROM Supplier
        LEFT OUTER JOIN
        SupParts
        ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
  FROM Supplier
       LEFT OUTER JOIN
       SupParts
       ON Supplier.supno = SupParts.supno
          AND qty < 200;

Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-92.
For example to find the students who have taken Math 101 and might
have taken Math 102:

SELECT C1.student, C1.math, C2.math
  FROM (SELECT * FROM Courses WHERE math = 101) AS C1
       LEFT OUTER JOIN
       (SELECT * FROM Courses WHERE math = 102) AS C2
       ON C1.student = C2.student;

Thank you for a very useful posting.

Best Regards,

Vince Kavanagh.

Show quoteHide quote

Very useful thanks.  I like your use of the term "diseased mutant
products"....


Seb

Memory problems using Sql Server 2000
Joing two tables but avoid cartesian product
Dynamic SQL Column Total Help
Datetime constraint: is there a better way to do this?
multiple inserts in transaction