SQL Query - FULL JOIN multiple tables but return NULL results

Question

Post reply

SQL Query - FULL JOIN multiple tables but return NULL results

glennyboy

Old Hand

Points: 348

September 19, 2020 at 4:53 am

#3789597

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 4 years, 2 months ago by glennyboy.

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

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".