Solved

SQL Query Help

Posted on 2014-02-18
2
216 Views
Last Modified: 2014-02-18
Hello there,

I have this sql query which is working as I expect but it is also get this null values for each product. How can I exclude those null qtyArrived and BatchNo

SELECT
        supplierOrderDetails.productID as productid,
        supplierOrder.id as supplierorderid,
        product.brandname as brandname,
        product.productcode as code,
        supplierOrderDetails.orderQty as orderQty,
        supplierOrderDetails.id as sodID,
        SUM(supplierOrderReceiveDetail.qtyArrived) as qtyArrived ,
        supplierOrderReceiveDetail.batchNo as BatchNo
    FROM
        supplierOrder  
    LEFT OUTER JOIN
        supplierOrderDetails  
            ON      (
                supplierOrderDetails.supplierOrderID = supplierOrder.ID
            )  
    LEFT OUTER JOIN
        product  
            ON  (
                product.id = supplierOrderDetails.productID
            )  
    LEFT OUTER JOIN
        supplierOrderReceive  
            ON         (
                supplierOrderReceive.supplierOrderID = supplierOrder.ID
            )  
    LEFT OUTER JOIN
        supplierOrderReceiveDetail  
            ON         (
                supplierOrderReceiveDetail.supplierOrderReceiveID = supplierOrderReceive.ID
            )           
            AND   (
                supplierOrderReceiveDetail.ProductID =supplierOrderDetails.ProductID
            )       
    WHERE
        supplierOrder.ID = 1  and supplierOrderDetails.isComplete  = 1
        GROUP BY
        supplierOrderDetails.productID,
        supplierOrderDetails.orderQty,
        product.brandname,
        product.productcode,
        supplierOrder.id,
        supplierOrderDetails.id,
        supplierOrderReceiveDetail.batchNo

Open in new window

2-18-2014-1-06-15-PM.gif
0
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39866865
change:
LEFT OUTER JOIN         supplierOrderReceiveDetail  

into
INNER JOIN         supplierOrderReceiveDetail  

to avoid non-matched rows.
however, if the columns are indeed null in that table, you need to add  this after the GROUP BY conditions:

HAVING SUM(supplierOrderReceiveDetail.qtyArrived) is not null
0
 

Author Closing Comment

by:zolf
ID: 39866967
cheers !!
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question