SQL Query - FULL JOIN multiple tables but return NULL results

  • glennyboy

    Old Hand

    Points: 345

    Good day!

    Need help with my sql query code;

    first FULL JOIN "ILEtransfer" no NULL result

    but im getting NULL result once i add a FULL JOIN "ILEmsales"

    Thank you in advance!

    here's my sql query code;

         ;WITH Barcodes AS
    (
    SELECT
    [BBI$Barcodes].[Item No_]
    ,[BBI$Barcodes].[Description]
    ,[BBI$Barcodes].[Variant Code]
    FROM [BBI$Barcodes]
    ),
    ILEtransfer AS
    (
    SELECT
    [BBI$Item Ledger Entry].[Entry Type]
    ,[BBI$Item Ledger Entry].[Location Code] AS [Location]
    ,[BBI$Item Ledger Entry].[Item No_]
    ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]
    ,[BBI$Item Ledger Entry].[Variant Code]
    ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [Delivery]

    FROM [BBI$Item Ledger Entry]
    WHERE
    [BBI$Item Ledger Entry].[Location Code]='HPGW'
    AND [BBI$Item Ledger Entry].[Entry Type] = '4'
    GROUP BY
    [BBI$Item Ledger Entry].[Location Code]
    ,[BBI$Item Ledger Entry].[Entry Type]
    ,[BBI$Item Ledger Entry].[Item No_]
    ,[BBI$Item Ledger Entry].[Variant Code]
    ),
    ILEmsales AS
    (
    SELECT
    [BBI$Item Ledger Entry].[Entry Type]
    ,[BBI$Item Ledger Entry].[Location Code] AS [Location]
    ,[BBI$Item Ledger Entry].[Item No_]
    ,MAX([BBI$Item Ledger Entry].[Description]) AS [Description]
    ,[BBI$Item Ledger Entry].[Variant Code]
    ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]

    FROM [BBI$Item Ledger Entry]
    WHERE
    [BBI$Item Ledger Entry].[Location Code]='HPGW'
    AND [BBI$Item Ledger Entry].[Entry Type] = '1'
    AND [BBI$Item Ledger Entry].[Document No_] NOT LIKE 'HP%'
    GROUP BY
    [BBI$Item Ledger Entry].[Location Code]
    ,[BBI$Item Ledger Entry].[Entry Type]
    ,[BBI$Item Ledger Entry].[Item No_]
    ,[BBI$Item Ledger Entry].[Variant Code]
    )
    SELECT DISTINCT
    BAR.[Item No_] AS [Item No_]
    ,BAR.[Description] AS [Description]
    ,BAR.[Variant Code] AS [Variant Code]
    ,ISNULL(ILETR.[Delivery],0) AS [Delivery]
    ,ISNULL(ILEMS.[MSales],0) AS [Sales]

    FROM [BBI$Barcodes] BAR

    FULL JOIN [ILEtransfer] ILETR
    ON ILETR.[Item No_]=BAR.[Item No_]
    AND ILETR.[Variant Code]=BAR.[Variant Code]

    FULL JOIN [ILEmsales] ILEMS
    ON ILEMS.[Item No_]=BAR.[Item No_]
    AND ILEMS.[Variant Code]=BAR.[Variant Code]

    WHERE
    ILETR.[Location]='HPGW'
    AND ILETR.[Entry Type]='4'
    OR ILEMS.[Entry Type]='1'

    sql query result NULL

    NULL

    • This topic was modified 1 month, 1 week ago by  glennyboy.
  • ScottPletcher

    SSC Guru

    Points: 98626

    That is normal behavior for a FULL JOIN.  You need to adjust the final SELECT to reflect the fact that any table's results could be NULL, like so:

    ...
    )
    SELECT DISTINCT
    ...
    COALESCE(BAR.[Item No_], ILETR.[Item No_], ILEMS.[Item No_]) AS [Item No_],
    ...
    COALESCE(BAR.[Variant Code], ILETR.[Variant Code], ILEMS.[Variant Code]) AS [Variant Code]
    ...

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply