none
SQL Server 2014 complained about original query but works with its new version RRS feed

  • Pergunta

  • Hi everybody,

    Found an interesting little issue today which I was about to post a question on until I changed the query a little bit and it worked. I'm not sure what was wrong in my original version and why SQL Server complained - can anyone explain?

    This is working version:

    ;WITH allTypes AS (
        SELECT 
        CAST(ROW_NUMBER() OVER (ORDER BY t.Id, a.Id) * -1 AS INT) AS [EmailTemplateId],
        t.Id AS TemplateTypeId, 
        t.[Description] AS TemplateTypeDescription, 
        a.Id AS AuthorizationStateId,
        a.[Description] AS AuthorizationStateDescription
        FROM dbo.EmailTemplateTypes t
        CROSS JOIN dbo.EmailAuthorizationStates a),
        selectedItem AS (SELECT items.department as Department, 
        items.category as Category,
        items.item as Item,
        DepartmeId AS DepartmentId,
        CategoryId,
        items.descrip AS ItemDescription
        FROM dbo.items WHERE item_id = @ItemId),
        itemEmailTemplates AS (
        SELECT lnk.*, 
        t.TemplateTypeId, t.AuthorizationStateId
        FROM dbo.EmailTemplatesLink lnk
        JOIN dbo.EmailTemplates t ON lnk.EmailTemplateId = t.Id
        WHERE lnk.ItemId = @ItemId)    
    
        SELECT allTypes.*, 
        ISNULL(iet.Id,0) AS Id,
        ISNULL(iet.IsEnabled, 1) AS IsEnabled,
        ISNULL(iet.ItemId, 0) AS ItemId,
        ISNULL(iet.PaymentPendingDay,0) AS PaymentPendingDay,
        ISNULL(iet.RenewalPendingDays,0) AS RenewalPendingDays,
        selectedItem.*
        FROM allTypes INNER JOIN selectedItem on 1=1 
        LEFT JOIN itemEmailTemplates iet ON allTypes.AuthorizationStateId = iet.AuthorizationStateId
        AND allTypes.TemplateTypeId = iet.TemplateTypeId 
        ORDER BY allTypes.TemplateTypeId, allTypes.AuthorizationStateId;

    My original version was FROM allTypes, selectedItem LEFT JOIN ...

     and the error I was getting was about allTypes columns not bound. Once I changed it to INNER JOIN on 1=1 (e.g. I really need a cross join between the two as they are unrelated), it worked.

    Do you see what may have been wrong in my original syntax? Is it just SQL Server being confused?

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    sexta-feira, 14 de fevereiro de 2020 20:35
    Moderador

Respostas

  • The comma operator binds harder than the other join operators.

    That is,

       FROM a, b LEFT JOIN c ON ...
    is the same as
      FROM a, (b left join c ON ...)

    And thus the join conditions for b and c cannot refer to a.

    If you wanted a cross join, you would use that operator:

      FROM a
      CROSS JOIN B
      LEFT JOIN c ON ...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    sexta-feira, 14 de fevereiro de 2020 23:00

Todas as Respostas

  • The comma operator binds harder than the other join operators.

    That is,

       FROM a, b LEFT JOIN c ON ...
    is the same as
      FROM a, (b left join c ON ...)

    And thus the join conditions for b and c cannot refer to a.

    If you wanted a cross join, you would use that operator:

      FROM a
      CROSS JOIN B
      LEFT JOIN c ON ...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    sexta-feira, 14 de fevereiro de 2020 23:00
  • I realized today I actually needed a left join with selectedItem as well because it's possible to run this query for a new item when we don't really have it yet. I'm running a test now and most likely will do code adjustment right after.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    domingo, 16 de fevereiro de 2020 01:22
    Moderador