SQL Query - FULL JOIN multiple tables but return NULL results

glennyboy 121 Reputation points
2020-09-19T04:44:40.817+00:00

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

25923-image.png

Thank you!

Developer technologies Transact-SQL
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,621 Reputation points
    2020-09-21T01:35:43.007+00:00

    Hi @glennyboy

    When using left join, right join, full (outer) join, it is possible to return NULL value, while (inner) join, cross join will not return NUll value.
    The following example is the difference between these joins, please refer to:

        --join  
        create table a  
        (id int,  
        name char(20))  
        create table b  
        (id int,  
        name varchar(200))  
        insert into a values(1,'Tom'),(2,'Jane'),(3,'John'),(4,'Eyre'),(5,'Andy')  
        insert into b values(1,'Jane'),(2,'Alva'),(3,'Tom'),(4,'Alice'),(5,'Eyre')  
    
     
    select * from a  
    select * from b  
    

    25965-image.png

    select * from a inner join b on a.name=b.name  
    

    26031-image.png

    select * from a left  join b on a.name=b.name  
    select * from a right join b on a.name=b.name  
    

    26041-image.png

    select * from a full outer join b on a.name=b.name  
    

    25972-image.png
    When using full join, all information of table a and table b will be returned. When there is no value that meets the on condition, a null value will be returned.

    select * from a cross join b   
    

    25908-image.png

    For more details, please refer to:Joins (SQL Server)

    If this doesn't solve your problem,please publish the table with the data and the output you want, so that the experts will get a right direction and can do some tests, which will help you solve your problem quickly.

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Best Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    2 people found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator
    2020-09-19T09:08:46.693+00:00

    What do you want to achieve? Why do you want to use a full join in the first place?

    Full outer joins are quite more advanced that regular left outer joins. And full joins over more than two tables is not a walk in the park at all. I know, because I once had reasons to write such a query, and it took me a quite a while to get it right.

    This is why I ask why you want to use a full join. If there is no good reason for it, you should not get into the complexities of it.


  2. Fiona Foley 1 Reputation point
    2020-09-19T12:05:20.81+00:00

    Hi.. I usually use a left outer join, with the primary table on the right and the table with multiple rows in the join on the right...

    You should be able to just change the full to left outer..

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.