Solved

MSSQL Query help

Posted on 2013-12-23
14
260 Views
Last Modified: 2014-01-05
Hello there,

I have this query which is returning some redundant data. Can somebody please help me to correct this query. I have also attached the table relationship.

SELECT
    dbo.supplierOrder.sectionID,
    dbo.supplierOrder.supplierID,
    dbo.supplierOrder.supplierOrderNo,
    dbo.supplierOrder.supplierOrderCreated,
    dbo.supplierOrder.supplierOrderConfirmStatus,
    dbo.supplierOrderDetails.productID,
    dbo.supplierOrderDetails.orderQty,
    dbo.supplierOrderReceive.invoiceno,
    dbo.supplierOrderReceive.supplierInvoiceno,
    dbo.supplierOrderReceive.ID,
    dbo.supplierOrderReceiveDetail.qtyArrived
FROM
    dbo.supplierOrder
INNER JOIN
    dbo.supplierOrderDetails
ON
    (
        dbo.supplierOrderDetails.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
        dbo.supplierOrderReceive
ON  
     (        
       dbo.supplierOrderReceive.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
        dbo.supplierOrderReceiveDetail
ON  
     (        
       dbo.supplierOrderReceiveDetail.supplierOrderReceiveID = dbo.supplierOrderReceive.ID)       
WHERE
    dbo.supplierOrder.ID = 1 ;

Open in new window

table-relationship.png
table-relation.png
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
  • 7
  • 2
  • 2
  • +2
14 Comments
 

Author Comment

by:zolf
ID: 39737401
here is the output that I get

which is not exactly what I was want. the last column qtyArrived should show only its related qty but here as you can see it is repeating for each of the productID

sectionID      supplierID      supplierOrderNo      supplierOrderCreated      supplierOrderConfirmStatus      productID      orderQty      invoiceno      supplierInvoiceno      ID      qtyArrived
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      8
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      8
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      223444      1      8
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      565333      2      1
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      565333      2      1
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      565333      2      1
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      565333      2      4
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      565333      2      4
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      565333      2      4
0
 
LVL 7

Expert Comment

by:Phil Davidson
ID: 39737455
I would use the keyword DISTINCT before
dbo.supplierOrderReceiveDetail.qtyArrived

Open in new window

0
 

Author Comment

by:zolf
ID: 39737458
thanks for your comment.can you please give me the full query.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 7

Expert Comment

by:Phil Davidson
ID: 39737459
SELECT
    dbo.supplierOrder.sectionID,
    dbo.supplierOrder.supplierID,
    dbo.supplierOrder.supplierOrderNo,
    dbo.supplierOrder.supplierOrderCreated,
    dbo.supplierOrder.supplierOrderConfirmStatus,
    dbo.supplierOrderDetails.productID,
    dbo.supplierOrderDetails.orderQty,
    dbo.supplierOrderReceive.invoiceno,
    dbo.supplierOrderReceive.supplierInvoiceno,
    dbo.supplierOrderReceive.ID,
   DISTINCT dbo.supplierOrderReceiveDetail.qtyArrived
FROM
    dbo.supplierOrder
INNER JOIN
    dbo.supplierOrderDetails
ON
    (
        dbo.supplierOrderDetails.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
        dbo.supplierOrderReceive
ON  
     (        
       dbo.supplierOrderReceive.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
        dbo.supplierOrderReceiveDetail
ON  
     (        
       dbo.supplierOrderReceiveDetail.supplierOrderReceiveID = dbo.supplierOrderReceive.ID)       
WHERE
    dbo.supplierOrder.ID = 1 ;

Open in new window

0
 

Author Comment

by:zolf
ID: 39737462
no this give me error

 Incorrect syntax near the keyword 'DISTINCT'.

I tried to add DISTINCT at the start and it ran the query but does not give me the desired result
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737496
I just verified the data you have provided , I am not able to get complete record duplication here
 ID and       qtyArrived
is differenct for all cloumns
0
 

Author Comment

by:zolf
ID: 39737521
what do you mean. i have printed my result,it is duplicating the data. for e.g. for all the productID it is repeating the same qtyArrived. my final result should return only 4 records.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737537
sectionID      supplierID      supplierOrderNo      supplierOrderCreated      supplierOrderConfirmStatus      productID      orderQty      invoiceno      supplierInvoiceno      ID      qtyArrived
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      223444      1      2

you mean to say you need one record for this ? what will be that
0
 

Author Comment

by:zolf
ID: 39737545
i am expecting this

sectionID      supplierID      supplierOrderNo      supplierOrderCreated      supplierOrderConfirmStatus      productID      orderQty      invoiceno      supplierInvoiceno      ID      qtyArrived
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      8
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      565333      2      1
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      565333      2      4
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39738751
Can you explain how you would derive those rows from the data you posted earlier?  I am not seeing any rhyme or reason.  Take that first row:
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      8

I assume it comes from the following rows:
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      8

Is it the"last" one that you are looking for?  If so, can you define the "last" one?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39738755
For the second row:
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2

I am assuming it is coming from:
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      8

But here you appear to have picked the "first" one.  

Perhaps it does not matter which one is returned.
0
 

Author Comment

by:zolf
ID: 39738869
thanks for your comments.
see the invoice numbers in the result.  the idea is to get the products which the supplier has sent to the client. the products could have come in one batch or in small batches. the productid 1 has come in 2 batches(the orderQty by the client was 3 but it qtyArrived in 2 batches of 1 and 2). see the attached file.
table-relationship.png
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39752347
You are missing an additional JOIN condition on dbo.supplierOrderReceiveDetail.productID = dbo.supplierOrderDetails.productID
For better readability, I have used table aliases. see this query.
SELECT SO.sectionID, 
       SO.supplierID, 
       SO.supplierOrderNo, 
       SO.supplierOrderCreated, 
       SO.supplierOrderConfirmStatus, 
       SOD.productID, 
       SOD.orderQty, 
       SOR.invoiceno, 
       SOR.supplierInvoiceno, 
       SOR.ID, 
       SORD.qtyArrived 
  FROM dbo.supplierOrder SO 
       INNER JOIN dbo.supplierOrderDetails SOD 
               ON SOD.supplierOrderID = SO.ID 
       INNER JOIN dbo.supplierOrderReceive SOR 
               ON SOR.supplierOrderID = SO.ID 
       INNER JOIN dbo.supplierOrderReceiveDetail SORD 
               ON SORD.supplierOrderReceiveID = SOR.ID 
                  AND SORD.productID = SOD.productID 
 WHERE SO.ID = 1; 

Open in new window

http://sqlfiddle.com/#!3/6cdf5/1
0
 

Author Closing Comment

by:zolf
ID: 39758505
thanks
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

691 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